How to return result set from PostgreSQL stored procedure?

Question

PostgreSQL supports stored procedure (not function) since version 11. I created a stored procedure like this:

CREATE OR REPLACE PROCEDURE get_user_list ()
  LANGUAGE SQL
  SECURITY DEFINER
  AS $$
  SELECT "id",
         "username",
         "display_name"
  FROM "user"
  ORDER BY "created_at" ASC;
$$;

But when I am trying to execute this stored procedure, it does not return any data:

postgres=# CALL get_user_list();
CALL
postgres=# SELECT * FROM get_user_list();
ERROR:  get_user_list() is a procedure
LINE 1: SELECT * FROM get_user_list();
                      ^
HINT:  To call a procedure, use CALL.

So the question is, how can a stored procedure returns its result set in PostgreSQL 11+?

You need to define what the stored procedure returns. See this question for example. — Joakim Danielson
– Joakim Danielson, Commented Jun 17, 2018 at 6:41
@JoakimDanielson: That misunderstanding is probably due to the widespread misnomer "stored procedure". SQL procedures are not functions. See: dba.stackexchange.com/a/194811/3684 — Erwin Brandstetter
– Erwin Brandstetter, Commented Jun 17, 2018 at 12:09
@ErwinBrandstetter SQL procedures are not functions - I wouldn't name it misnomer. Both Oracle and SQL Server support returning resultset from stored procedure. — Lukasz Szozda
– Lukasz Szozda, Commented May 12, 2019 at 9:57

Kamil Gosciminski · Accepted Answer · 2018-06-17 06:58:38Z

7

Following the docs on Postgres 11 (bold emphasis mine):

A procedure does not have a return value. A procedure can therefore end without a RETURN statement. If a RETURN statement is desired to exit the code early, then NULL must be returned. Returning any other value will result in an error.

You could mark your parameters as output though, so that they would behave like variables.

On a side note there's normally a distinction within other DBMS that functions can only call SELECT statements and should not modify data while procedures should be handling the data manipulation and data definition languages (DML, DDL). This concludes (in my opinion) that creating a procedure to simply perform a stable (*) select statement is not the desired technique.

(*) Read more on function volatility here.

answered Jun 17, 2018 at 6:58

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Falchio · Accepted Answer · 2023-05-24 16:23:04Z

1

In case if you have one select in procedure to get result set you can create view like this:

CREATE OR REPLACE VIEW get_user_list as 
  SELECT "id",
         "username",
         "display_name"
  FROM "user"
  ORDER BY "created_at" ASC;

and then select * from get_user_list to get result set.

edited May 24, 2023 at 16:23

Falchio

7408 silver badges21 bronze badges

answered Feb 22, 2023 at 7:56

Igor

2,5681 gold badge20 silver badges20 bronze badges

Collectives™ on Stack Overflow

How to return result set from PostgreSQL stored procedure?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related