[SOLVED] How to return result set from PostgreSQL stored procedure?

How to return result set from PostgreSQL stored procedure?

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+?

Solution

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.