Postgresql中return setof函数的使用方法与实例

前言

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存在

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

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"