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

Question

提问by David

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

这个怎么写正确？

Answer 1

采纳答案by araqnid

(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 ;
 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?)

（不太清楚为什么需要额外的 ::text 强制转换……默认情况下，'1' 可能是 varchar？）

Answer 2

回答by Erwin Brandstetter

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

^{所有以前存在的答案都已经过时或开始时效率低下。}

Assuming you want to return three integercolumns.

假设您要返回三integer列。

PL/pgSQL function

PL/pgSQL 函数

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) -- 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;

In Postgres 9.6 or later you can also add PARALLEL SAFE.

在 Postgres 9.6 或更高版本中，您还可以添加PARALLEL SAFE.

Call:

称呼：

SELECT * FROM f_foo();

Major points

要点

Use RETURNS TABLEto define an ad-hoc row type to return.
Or RETURNS SETOF mytblto use a pre-defined row type.
Use RETURN QUERYto return multiple rows with one command.
Use a VALUESexpression to enter multiple rows manually. This is standard SQL and has been around for ever.
Ifyou actually need a parameter, use a parameter name (open_id numeric)instead of ALIAS, which is discouraged. In the example the parameter wasn't used and just noise ...
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.
ROWS 3is optional, but since we knowhow many rows are returned, we might as well declare it to Postgres. Can help the query planner to pick the best plan.

使用RETURNS TABLE来定义一个特设排式返回。
或者RETURNS SETOF mytbl使用预定义的行类型。
用于RETURN QUERY通过一个命令返回多行。
使用VALUES表达式手动输入多行。这是标准的SQL和一直围绕永远。
如果您确实需要一个参数，请使用参数名称(open_id numeric)而不是ALIAS，这是不鼓励的。在这个例子中，没有使用参数，只是噪音......
不需要双引号完全合法的标识符。双引号只需要强制其他非法名称（大小写混合、非法字符或保留字）。
函数波动性可以是IMMUTABLE，因为结果永远不会改变。
ROWS 3是可选的，但既然我们知道返回了多少行，我们不妨将它声明给 Postgres。可以帮助查询计划者挑选最佳计划。

Simple SQL

简单的 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

SQL函数

You can wrap it into a simple SQL functioninstead:

您可以将其包装成一个简单的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;

Answer 3

回答by araqnid

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()

Answer 4

回答by Brad Holbrook

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

Answer 5

回答by Brad Holbrook

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;

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

提问by David

采纳答案by araqnid

回答by Erwin Brandstetter

PL/pgSQL function

PL/pgSQL 函数

Major points

要点

Simple SQL

简单的 SQL

SQL function

SQL函数

回答by araqnid

回答by Brad Holbrook

回答by Brad Holbrook

相关推荐

最近更新

标签

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

提问by David

采纳答案by araqnid

回答by Erwin Brandstetter

PL/pgSQL function

PL/pgSQL 函数

Major points

要点

Simple SQL

简单的 SQL

SQL function

SQL函数

回答by araqnid

回答by Brad Holbrook

回答by Brad Holbrook

相关推荐

SQL 在“插入表值（）”语句中处理标识列？

Oracle 11g：ORA-00604：递归 SQL 级别 1 发生错误

SQL 有没有办法在整个过程中保持变量？

SQL 如何对 string_agg() 的结果进行排序

相关推荐

最近更新

标签