前言
Postgresql中包含两类setof函数：
- SQL函数：https://www.postgresql.org/docs/current/xfunc-sql.html
- PLPGSQL函数：https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
本文只关注PLPGSQL中的return setof的使用方法。
总结
假设类型foo存在
代码语言：javascript复制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 | 结果 |
|---|---|---|---|
1 | foo | foo%rowtype | 支持 |
2 | foo | record | 支持 |
3 | foo | foo | 支持 |
4 | record | foo%rowtype | ERROR: materialize mode required, but it is not allowed in this context |
5 | record | record | ERROR: materialize mode required, but it is not allowed in this context |
6 | record | foo | ERROR: materialize mode required, but it is not allowed in this context |
7 | foo%rowtype | 语法不支持 |
实例1
代码语言：javascript复制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
代码语言：javascript复制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
代码语言：javascript复制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
代码语言：javascript复制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
代码语言：javascript复制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
代码语言：javascript复制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"
