如何从PostgreSQL存储过程中返回结果集？

Question

自从11版本开始，PostgreSQL支持存储过程（不是函数）。我创建了一个类似于这样的存储过程：

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

但是当我尝试执行这个存储过程时，它没有返回任何数据：

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.

那么问题来了，存储过程在PostgreSQL 11+中如何返回其结果集呢？

- Fong-Wan Chau

---

你需要定义存储过程返回的内容。可以参考这个问题。 - Joakim Danielson

@JoakimDanielson：这种误解可能是由于广泛使用的错误术语“存储过程”造成的。SQL过程不是函数。请参见：https://dba.stackexchange.com/a/194811/3684 - Erwin Brandstetter

SQL程序并不是函数。我不会称之为误称。Oracle和SQL Server都支持从存储过程返回结果集。 - Lukasz Szozda

Answer 1

根据Postgres 11上的文档（加粗是我强调的）：

过程没有返回值。因此，过程可以在没有RETURN语句的情况下结束。如果需要使用RETURN语句提前退出代码，则必须返回NULL。返回任何其他值都会导致错误。

您可以将参数标记为输出，以便它们像变量一样运行。

顺便提一下，其他DBMS通常区分函数和过程：函数只能调用SELECT语句，不应修改数据，而过程应处理数据操作和数据定义语言（DML，DDL）。这表明（在我看来），仅为执行稳定的(*)选择语句创建过程并不是所需的技术。

（*）请在此处阅读有关函数波动性的更多信息。

- Kamil Gosciminski

Answer 2

如果您在存储过程中只有一个选择语句来获取结果集，您可以创建如下视图：

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

然后使用select * from get_user_list来获取结果集。

- Igor