Postgresql中包含两类setof函数:
本文只关注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');
实例 | 函数头中的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 | 语法不支持 |
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
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
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
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"
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"
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"