postgresql 如何从 PL/pgSQL 函数返回多行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17244256/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to return multiple rows from PL/pgSQL function?
提问by Asma Zubair
I have spent good amount of time trying to figure it out and I haven't been able to resolve it. So, I need your help please.
我花了很多时间试图弄清楚它，但我一直无法解决它。所以，我需要你的帮助。
I am trying to write a PL/pgSQL function that returns multiple rows. The function I wrote is shown below. But it is not working.
我正在尝试编写一个返回多行的 PL/pgSQL 函数。我写的函数如下所示。但它不起作用。
CREATE OR REPLACE FUNCTION get_object_fields()
RETURNS SETOF RECORD
AS
$$
DECLARE result_record keyMetrics;
BEGIN
return QUERY SELECT department_id into result_record.visits
from fact_department_daily
where report_date='2013-06-07';
--return result_record;
END
$$ LANGUAGE plpgsql;
SELECT * FROM get_object_fields;
It is returning this error:
它正在返回此错误：
ERROR: RETURN cannot have a parameter in function returning set;
use RETURN NEXT at or near "QUERY"
错误：RETURN 在函数返回集合中不能有参数；
在“QUERY”处或附近使用 RETURN NEXT
采纳答案by Pavel Stehule
I see more bugs:
我看到更多错误：
first, a SET RETURNING FUNCTIONS call has following syntax
首先，SET RETURNING FUNCTIONS 调用具有以下语法
SELECT * FROM get_object_fields()
second - RETURN QUERY forwards query result to output directly. You cannot store this result to variable - it is not possible ever in PostgreSQL now.
第二 - RETURN QUERY 将查询结果直接转发到输出。您不能将此结果存储到变量中 - 现在在 PostgreSQL 中是不可能的。
BEGIN RETURN QUERY SELECT ....; -- result is forwarded to output directly RETURN; -- there will not be any next result, finish execution END;
third - these simple functions is better to implement in SQL languages
第三 - 这些简单的功能最好用 SQL 语言实现
CREATE OR REPLACE FUNCTION get_object_fields() RETURNS SETOF RECORD AS $$ SELECT department_id WHERE ... $$ LANGUAGE sql STABLE;
回答by Erwin Brandstetter
After fixing the bugs @Pavel already pointed out, also define your return typeproperly, or you have to provide a column definition list with every call.
在修复@Pavel 已经指出的错误之后，还要正确定义您的返回类型，或者您必须在每次调用时提供一个列定义列表。
This call:
这个电话：
SELECT * FROM get_object_fields()
demands that Postgres knows how to expand *. Since you are returning anonymous records, you'd get an exception:
要求 Postgres 知道如何扩展*。由于您要返回匿名记录，您会得到一个例外：
ERROR: a column definition list is required for functions returning "record"
错误：返回“记录”的函数需要列定义列表
Oneway (of several) to fix this is with RETURNS TABLE(Postgres 8.4+):
解决此问题的一种方法（几种）是使用RETURNS TABLE（Postgres 8.4+）：
CREATE OR REPLACE FUNCTION get_object_fields()
RETURNS TABLE (department_id int) AS
$func$
BEGIN
RETURN QUERY
SELECT department_id
FROM fact_department_daily
WHERE report_date = '2013-06-07';
END
$func$ LANGUAGE plpgsql;Works for SQL functions just the same.
对 SQL 函数同样适用。
More details and links in this closely related answer:
PostgreSQL: ERROR: 42601: a column definition list is required for functions returning "record"
此密切相关的答案中的更多详细信息和链接：
PostgreSQL：错误：42601：返回“记录”的函数需要列定义列表
回答by superlogical
Here's one way
这是一种方法
drop function if exists get_test_type();
drop type if exists test_comp;
drop type if exists test_type;
drop type if exists test_person;
create type test_type as (
foo int,
bar int
);
create type test_person as (
first_name text,
last_name text
);
create type test_comp as
(
prop_a test_type[],
prop_b test_person[]
);
create or replace function get_test_type()
returns test_comp
as $$
declare
a test_type[];
b test_person[];
x test_comp;
begin
a := array(
select row (m.message_id, m.message_id)
from message m
);
-- alternative 'strongly typed'
b := array[
row('Bob', 'Jones')::test_person,
row('Mike', 'Reid')::test_person
]::test_person[];
-- alternative 'loosely typed'
b := array[
row('Bob', 'Jones'),
row('Mike', 'Reid')
];
-- using a select
b := array (
select row ('Jake', 'Scott')
union all
select row ('Suraksha', 'Setty')
);
x := row(a, b);
return x;
end;
$$
language 'plpgsql' stable;
select * from get_test_type();
