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

查看:33
本文介绍了从函数返回记录集(虚拟表)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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

想象一下:

CREATE OR REPLACE FUNCTION "public"."storeopeninghours_tostring"(数字)返回记录集 AS宣布1 美元的 open_id 别名;returnrecords 记录集;开始插入 returnrecords('1', '2', '3');插入 returnrecords('3', '4', '5');插入 returnrecords('3', '4', '5');RETURN 退货记录;结尾;

这是怎么写的?

解决方案

(这都是用 postgresql 8.3.7 测试的——你有更早的版本吗?看看你对ALIAS FOR $1"的使用)

CREATE OR REPLACE FUNCTION storeopeninghours_tostring(numeric)返回 SETOF 记录为 $$宣布1 美元的 open_id 别名;结果记录;开始返回查询选择1"、2"、3";返回查询选择3"、4"、5";返回查询选择3"、4"、5";结尾$$;

如果您有记录或行变量要返回(而不是查询结果),请使用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)返回 SETOF 记录语言 'plpgsql' STABLE STRICT AS $$开始返回查询 SELECT '1'::text, '2'::text, '3'::text;返回查询 SELECT '3'::text, '4'::text, '5'::text;返回查询 SELECT '3'::text, '4'::text, '5'::text;结尾$$;

(不太清楚为什么需要额外的 ::text 强制转换……默认情况下,'1' 可能是 varchar?)

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.

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?

解决方案

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

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

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

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

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆