PostgreSQL function返回多行的操作_PostgreSQL_

 postgres=# create table tb1(id integer,name character varying); CREATE TABLE postgres=# postgres=# insert into tb1 select generate_series(1,5),'aa'; INSERT 0 5 

 create or replace function func01()returns setof character varying as $$ declare n character varying; begin for i in 1..5 loop select name into n from tb1 where id=i; return next n; end loop; end $$ language plpgsql;

 create or replace function func02(out character varying)returns setof character varying as $$ begin for i in 1..5 loop select name into $1from tb1 where id=i; return next; end loop; end $$ language plpgsql;

 create or replace function func03()returns setof character varying as $$ begin for i in 1..5 loop return query(select name from tb1 where id=i); end loop; end $$language plpgsql;

 create or replace function func04()RETURNS SETOF RECORD as $$ declare r record; begin for i in 1..5 loop select * into r from tb1 where id=i; return next r; end loop; end; $$language plpgsql;

 postgres=# select func04(); ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function func04() line 7 at RETURN NEXT

 If you call your set-returning function the wrong way (IOW the way you might normally call a function), you will get this error message: Set-valued function called in context that cannot accept a set. Incorrect: select sr_func(arg1, arg2, …); Correct: select * from sr_func(arg1, arg2, …);

 postgres=# select * from func04(); ERROR: a column definition list is required for functions returning "record" LINE 1: select * from func04();

 postgres=# select * from func04() as t(id integer,name character varying); id | name ----+------ 1 | aa 2 | aa 3 | aa 4 | aa 5 | aa (5 rows)

 create or replace function func05(out out_id integer,out out_name character varying)returns setof record as $$ declare r record; begin for i in 1..5 loop select * into r from tb1 where id=i; out_id:=r.id; out_name:=r.name; return next; end loop; end; $$language plpgsql;

 postgres=# select * from func05(); id | name ----+------ 1 | aa 2 | aa 3 | aa 4 | aa 5 | aa (5 rows)

 create or replace function func06()returns setof record as $$ begin for i in 1..5 loop return query(select id,name from tb1 where id=i); end loop; end; $$language plpgsql;

 postgres=# select * from func06() as t(id integer,name character varying); id | name ----+------ 1 | aa 2 | aa 3 | aa 4 | aa 5 | aa (5 rows)

 mytest=# create or replace function test_0830_5() returns setof test mytest-# as $$ mytest$# DECLARE mytest$# r test%rowtype; -- 将 mytest$# BEGIN mytest$# FOR r IN mytest$# SELECT * FROM test WHERE id > 0 mytest$# LOOP mytest$# RETURN NEXT r; mytest$# END LOOP; mytest$# RETURN; mytest$# END mytest$# $$ language plpgsql; CREATE FUNCTION mytest=# select test_0830_5(1); test_0830_5 ------------------------------------------ (2,abcabc,"2018-08-30 09:26:14.392187") ...... (11,abcabc,"2018-08-30 09:26:14.392187") (10 rows) mytest=# select * from test_0830_5(); id | col1 | col2 ----+--------+---------------------------- 2 | abcabc | 2018-08-30 09:26:14.392187 ...... 11 | abcabc | 2018-08-30 09:26:14.392187 (10 rows)

 mytest=# CREATE OR REPLACE FUNCTION test_0830_6(date) RETURNS SETOF integer AS $$ mytest$# BEGIN mytest$# RETURN QUERY SELECT id mytest$# FROM test mytest$# WHERE col2 >= $1 mytest$# AND col2 < ($1 + 1); mytest$# IF NOT FOUND THEN mytest$# RAISE EXCEPTION 'No id at %.', $1; mytest$# END IF; mytest$# RETURN; mytest$# END mytest$# $$ mytest-# LANGUAGE plpgsql; CREATE FUNCTION mytest=# select test_0830_6('2018-08-30'); test_0830_6 ------------- 2 ...... 11 (10 rows)