从函数返回记录集（虚拟表）

2024-02-20

我需要一个 Postgres 函数来返回一个包含自定义内容的虚拟表（就像在 Oracle 中一样）。该表有 3 列和未知的行数。

我只是在互联网上找不到正确的语法。

想象一下：

CREATE OR REPLACE FUNCTION "public"."storeopeninghours_tostring" (numeric)
  RETURNS setof record AS
DECLARE
  open_id ALIAS FOR $1;
  returnrecords setof record;
BEGIN
  insert into returnrecords('1', '2', '3');
  insert into returnrecords('3', '4', '5');
  insert into returnrecords('3', '4', '5');
  RETURN returnrecords;
END;

这句话怎么写才正确呢？

^{All previously existing answers are outdated or were inefficient to begin with.}

假设你想返回三个integer列。

PL/pgSQL 函数

以下是如何使用现代 PL/pgSQL（PostgreSQL 8.4 或更高版本）执行此操作：

CREATE OR REPLACE FUNCTION f_foo() -- (open_id numeric) -- parameter not used
  RETURNS TABLE (a int, b int, c int) AS
$func$
BEGIN
RETURN QUERY VALUES
  (1,2,3)
, (3,4,5)
, (3,4,5)
;
END
$func$  LANGUAGE plpgsql IMMUTABLE ROWS 3;

在 Postgres 9.6 或更高版本中，您还可以添加PARALLEL SAFE https://www.postgresql.org/docs/current/sql-createfunction.html.

Call:

SELECT * FROM f_foo();

主要观点

Use RETURNS TABLE https://www.postgresql.org/docs/current/sql-createfunction.html定义要返回的临时行类型。
Or RETURNS SETOF mytbl使用预定义的行类型。
Use RETURN QUERY https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING使用一个命令返回多行。
Use a VALUES https://www.postgresql.org/docs/current/sql-values.html表达式手动输入多行。这是标准 SQL 并且已经存在for ever.
If你实际上需要一个参数，使用参数名称(open_id numeric)代替ALIAS，这是不鼓励的 https://www.postgresql.org/docs/current/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIAS。在示例中，未使用参数，只是噪声......
不需要双引号完全合法的标识符。仅在强制使用其他非法名称（大小写混合、非法字符或保留字）时才需要使用双引号。
函数波动性可表示为IMMUTABLE https://stackoverflow.com/questions/28569415/how-do-immutable-stable-and-volatile-keywords-effect-behaviour-of-function/28573737#28573737，因为结果永远不会改变。
ROWS 3是可选的，但因为我们know返回了多少行，我们不妨向 Postgres 声明一下。可以帮助查询计划者选择最佳计划。

简单的SQL

对于像这样的简单情况，您可以使用普通的 SQL 语句：

VALUES (1,2,3), (3,4,5), (3,4,5)

或者，如果您想要（或必须）定义特定的列名称和类型：

SELECT *
FROM  (
   VALUES (1::int, 2::int, 3::int)
        , (3, 4, 5)
        , (3, 4, 5)
   ) AS t(a, b, c);

SQL函数

你可以将它包装成一个简单的SQL函数 https://www.postgresql.org/docs/current/xfunc-sql.html反而：

CREATE OR REPLACE FUNCTION f_foo()
   RETURNS TABLE (a int, b int, c int) AS
$func$
   VALUES (1, 2, 3)
        , (3, 4, 5)
        , (3, 4, 5);
$func$  LANGUAGE sql IMMUTABLE ROWS 3;

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

sql

postgresql

storedprocedures

PLpgSQL