前言
Postgresql中包含两类setof函数：
SQL函数：https://www.postgresql.org/docs/current/xfunc-sql.htmlPLPGSQL函数：https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
本文只关注PLPGSQL中的return setof的使用方法。
总结
假设类型foo存在
DROP TABLE foo cascade;
CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');
支持实例1-3场景，函数定义中的返回值不能是占位符类型record（实例4-6）。且函数内部的return next的类型必须和函数头中定义的RETURNS SETOF的类型相同。
实例函数头中的RETURNS SETOF函数内部的RETURN NEXT结果1foofoo%rowtype支持2foorecord支持3foofoo支持4recordfoo%rowtypeERROR: materialize mode required, but it is not allowed in this context5recordrecordERROR: materialize mode required, but it is not allowed in this context6recordfooERROR: materialize mode required, but it is not allowed in this context7foo%rowtype语法不支持
实例1
DROP TABLE foo cascade;
CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');
CREATE OR REPLACE FUNCTION f1() RETURNS SETOF foo AS
$BODY$
DECLARE
r foo%rowtype;
BEGIN
FOR r IN
SELECT * FROM foo WHERE fooid > 0
LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE plpgsql;
select f1();
f1
-------------
(1,2,three)
(4,5,six)
SELECT * FROM f1();
fooid | foosubid | fooname
-------+----------+---------
1 | 2 | three
4 | 5 | six
实例2
DROP TABLE foo cascade;
CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');
CREATE OR REPLACE FUNCTION f2() RETURNS SETOF foo AS
$BODY$
DECLARE
r record;
BEGIN
FOR r IN
SELECT * FROM foo WHERE fooid > 0
LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE plpgsql;
select f2();
f2
-------------
(1,2,three)
(4,5,six)
SELECT * FROM f2();
fooid | foosubid | fooname
-------+----------+---------
1 | 2 | three
4 | 5 | six
实例3
DROP TABLE foo cascade;
CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');
CREATE OR REPLACE FUNCTION f3() RETURNS SETOF foo AS
$BODY$
DECLARE
r foo;
BEGIN
FOR r IN
SELECT * FROM foo WHERE fooid > 0
LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE plpgsql;
select f3();
f3
-------------
(1,2,three)
(4,5,six)
SELECT * FROM f3();
fooid | foosubid | fooname
-------+----------+---------
1 | 2 | three
4 | 5 | six
实例4
DROP TABLE foo cascade;
CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');
CREATE OR REPLACE FUNCTION f4() RETURNS SETOF record AS
$BODY$
DECLARE
r foo%rowtype;
BEGIN
FOR r IN
SELECT * FROM foo WHERE fooid > 0
LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE plpgsql;
select f4();
ERROR: materialize mode required, but it is not allowed in this context
SELECT * FROM f4();
ERROR: a column definition list is required for functions returning "record"
实例5
DROP TABLE foo cascade;
CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');
CREATE OR REPLACE FUNCTION f5() RETURNS SETOF record AS
$BODY$
DECLARE
r record;
BEGIN
FOR r IN
SELECT * FROM foo WHERE fooid > 0
LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE plpgsql;
select f5();
ERROR: materialize mode required, but it is not allowed in this context
SELECT * FROM f5();
ERROR: a column definition list is required for functions returning "record"
实例6
DROP TABLE foo cascade;
CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');
CREATE OR REPLACE FUNCTION f6() RETURNS SETOF record AS
$BODY$
DECLARE
r foo;
BEGIN
FOR r IN
SELECT * FROM foo WHERE fooid > 0
LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE plpgsql;
select f6();
ERROR: materialize mode required, but it is not allowed in this context
SELECT * FROM f6();
ERROR: a column definition list is required for functions returning "record"
文章链接
发表评论