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

I need a Postgres function to return a virtual table (like in Oracle) with custom content. The table would have 3 columns and an unknown number of rows.

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

I just couldn't find the correct syntax on the internet.

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

Imagine this:

想象一下：

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;

How is this written correctly?

这怎么写的正确？

5 个解决方案

#1

(This is all tested with postgresql 8.3.7-- do you have an earlier version? just looking at your use of "ALIAS FOR $1")

（这都是用postgresql 8.3.7测试的 - 你有早期版本吗？只看你使用“ALIAS FOR $ 1”）

CREATE OR REPLACE FUNCTION storeopeninghours_tostring(numeric)
 RETURNS SETOF RECORD AS $$
DECLARE
 open_id ALIAS FOR $1;
 result RECORD;
BEGIN
 RETURN QUERY SELECT '1', '2', '3';
 RETURN QUERY SELECT '3', '4', '5';
 RETURN QUERY SELECT '3', '4', '5';
END
$$;

If you have a record or row variable to return (instead of a query result), use "RETURN NEXT" rather than "RETURN QUERY".

如果要返回记录或行变量（而不是查询结果），请使用“RETURN NEXT”而不是“RETURN QUERY”。

To invoke the function you need to do something like:

要调用该函数，您需要执行以下操作：

select * from storeopeninghours_tostring(1) f(a text, b text, c text);

So you have to define what you expect the output row schema of the function to be in the query. To avoid that, you can specify output variables in the function definition:

因此，您必须定义您希望函数的输出行模式在查询中的内容。为避免这种情况，您可以在函数定义中指定输出变量：

CREATE OR REPLACE FUNCTION storeopeninghours_tostring(open_id numeric, a OUT text, b OUT text, c OUT text)
 RETURNS SETOF RECORD LANGUAGE 'plpgsql' STABLE STRICT AS $$
BEGIN
 RETURN QUERY SELECT '1'::text, '2'::text, '3'::text;
 RETURN QUERY SELECT '3'::text, '4'::text, '5'::text;
 RETURN QUERY SELECT '3'::text, '4'::text, '5'::text;
END
$$;

(not quite sure why the extra ::text casts are required... '1' is a varchar by default maybe?)

（不太确定为什么extra :: text强制转换是必需的...'1'默认情况下是varchar？）

#2

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

所有当前存在的答案都已过时或开始时效率低下。

Assuming you want to return three integer columns.

假设您要返回三个整数列。

PL/pgSQL function

Here's how you do it with modern PL/pgSQL (PostgreSQL 8.4 or later):

以下是使用现代PL / pgSQL（PostgreSQL 8.4或更高版本）的方法：

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

Call:

呼叫：

SELECT * FROM f_foo(1);

Major points

• Use RETURNS TABLE to define an ad-hoc row type to return.
  Or RETURNS SETOF mytbl to use a pre-defined row type.

  使用RETURNS TABLE定义要返回的临时行类型。或者RETURNS SETOF mytbl使用预定义的行类型。

• Use RETURN QUERY to return multiple rows with one command.

  使用RETURN QUERY通过一个命令返回多行。

• Use a VALUES expression to enter multiple rows manually. This is standard SQL and has been around for ever.

  使用VALUES表达式手动输入多行。这是标准的SQL，并且一直存在。

• Use a parameter name (open_id numeric) instead of ALIAS, which is discouraged for standard parameter names. In the example the parameter isn't used and just noise ...

  使用参数名称（open_id数字）而不是ALIAS，不鼓励使用标准参数名称。在示例中，参数未使用，只是噪音......

• No need for double-quoting perfectly legal identifiers. Double-quotes are only needed to force otherwise illegal names (mixed-case, illegal characters or reserved words).

  无需双引号完全合法的标识符。仅需要双引号来强制使用其他非法名称（混合大小写，非法字符或保留字）。

• Function volatility can be IMMUTABLE, since the result never changes.

  函数波动率可以是IMMUTABLE，因为结果永远不会改变。

• ROWS 3 is optional, but since we know how many rows are returned, we might as well declare it to Postgres. Can help the query planner to pick the best plan.

  ROWS 3是可选的，但由于我们知道返回了多少行，我们不妨将其声明为Postgres。可以帮助查询计划员选择最佳计划。

Simple SQL

For a simple case like this, you can use a plain SQL statement instead:

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

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

Or, if you want (or have) to define specific column names and types:

或者，如果您希望（或有）定义特定的列名称和类型：

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

SQL function

You can wrap it into a simple SQL function. Example without function parameter, since it is not used:

您可以将其包装到一个简单的SQL函数中。没有函数参数的示例，因为它没有使用：

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;

#3

I use temporary tables quite a bit in my functions. You need to create a return type on the database and then create a variable of that type to return. Below is sample code that does just that.

我在函数中使用临时表很多。您需要在数据库上创建返回类型，然后创建要返回的该类型的变量。下面是示例代码。

CREATE TYPE storeopeninghours_tostring_rs AS
(colone text,
 coltwo text,
 colthree text
);

CREATE OR REPLACE FUNCTION "public"."storeopeninghours_tostring" () RETURNS setof storeopeninghours_tostring_rs AS
$BODY$
DECLARE
  returnrec storeopeninghours_tostring_rs;
BEGIN
    BEGIN 
        CREATE TEMPORARY TABLE tmpopeninghours (
            colone text,
            coltwo text,
            colthree text
        );
    EXCEPTION WHEN OTHERS THEN
        TRUNCATE TABLE tmpopeninghours; -- TRUNCATE if the table already exists within the session.
    END;
    insert into tmpopeninghours VALUES ('1', '2', '3');
    insert into tmpopeninghours VALUES ('3', '4', '5');
    insert into tmpopeninghours VALUES ('3', '4', '5');

    FOR returnrec IN SELECT * FROM tmpopeninghours LOOP
        RETURN NEXT returnrec;
    END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;


select * from storeopeninghours_tostring()

#4

To those who have landed here looking for the MSSQL equivalent of creating a temp table and dumping out its records as your return... that doesn't exist in PostgreSQL :( - you must define the return type. There are two ways to do this, at the time of the function creation or at the time of the query creation.

对于那些已经登陆这里寻找MSSQL相当于创建临时表并将其记录转储为返回的人...在PostgreSQL中不存在:( - 你必须定义返回类型。有两种方法可以做这，在创建函数时或在创建查询时。

See here: http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions

请看：http：//wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions

#5

CREATE OR REPLACE FUNCTION foo(open_id numeric, OUT p1 varchar, OUT p2 varchar, OUT p3 varchar) RETURNS SETOF RECORD AS $$
BEGIN
  p1 := '1'; p2 := '2'; p3 := '3';
  RETURN NEXT; 
  p1 := '3'; p2 := '4'; p3 := '5';
  RETURN NEXT; 
  p1 := '3'; p2 := '4'; p3 := '5';
  RETURN NEXT; 
  RETURN;
END;
$$ LANGUAGE plpgsql;