提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  其他

当前栏目

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

2023-03-07 09:41:38 时间

前言

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"

相关文章