从函数返回setof记录(虚拟表) [英] Return setof record (virtual table) from function

问题描述

我需要一个Postgres函数来返回带有自定义内容的虚拟表(例如在Oracle中).该表将有3列，行数未知.

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.

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

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

想象一下:

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;

如何正确书写?

推荐答案

(已使用PostgreSQL 8.3.7进行了全部测试-您是否有更早的版本?只是查看您对"ALIAS FOR $ 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")

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".

If you have a record or row variable to return (instead of a query result), use "RETURN NEXT" rather than "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
$$;

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

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

这篇关于从函数返回setof记录(虚拟表)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！