sql 从函数返回setof记录(虚拟表)

我需要一个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;

如何正确书写？

以前存在的所有答案过时或效率低下。

假设您要返回三个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。

呼叫：

SELECT * FROM f_foo();

要点

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

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

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

• 如果确实需要参数，请不要使用参数名称(open_id numeric)而不是ALIAS。在该示例中，未使用参数，而仅使用噪音...

• 无需双引号完全合法的标识符。仅需使用双引号来强制否则使用非法名称(大小写混合，非法字符或保留字)。 (短码网-duanma.net)

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

• ROWS 3是可选的，但是由于我们知道返回了多少行，因此我们最好将其声明给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函数中：

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;

(这全部在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
$$;

如果您有要返回的记录或行变量(而不是查询结果)，请使用" RETURN NEXT"而不是" RETURN QUERY"。

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

SELECT * FROM storeopeninghours_tostring(1) f(a text, b text, c text);

因此，您必须定义期望在查询中显示函数的输出行架构的内容。为了避免这种情况，可以在函数定义中指定输出变量：

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

(不太确定为什么需要额外的:: text强制转换。默认情况下，" 1"默认是varchar吗？)

• 从storeopeninghours_tostring(1)中选择* f(a文本，b文本，c文本);" f("是做什么的？我尝试用d和其他字符替换f，它们似乎都可以工作。
• f(只是从函数中命名结果集-就像在" from"子句中别名表一样。

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

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

• 我喜欢这个解决方案。 然后，您可以轻松地对结果进行排序，然后再返回等。

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

看这里：
http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions

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 从函数返回setof记录(虚拟表)》相关内容，希望大家喜欢。