Postgres 11 存储过程不返回结果 [英] Postgres 11 Stored Procedure not returning results

问题描述

在 Postgres 11 中，存储过程是否不打算返回结果集?因为当我们运行以下时，它说它运行成功但没有返回任何结果.

In Postgres 11, are stored procedures not intended to return result sets? Because when we run the following, it says it ran successfully but no results are returned.

CREATE OR REPLACE PROCEDURE test()
LANGUAGE sql
AS $$
  SELECT * from aTable;
$$;

call test();

推荐答案

Postgresql 过程最多只能返回一个值:

Postgresql procedures can return at most a single value:

CREATE PROCEDURE test (INOUT x int)
LANGUAGE sql
AS $$
    SELECT x;
$$;

# call test (1);
 x
---
 1
(1 row)

Postgresql 函数更加灵活:

Postgresql functions are much more flexible:

create or replace function test()
RETURNS setof atable
language sql
as $$
select * from atable;
$$;

程序的优点是可以控制事务.如果您不需要它，那么使用函数可能会更好.

The advantage of procedures is that they can control transactions. If you don't need that, you are likely better off with a function.

这篇关于Postgres 11 存储过程不返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！