PostgreSQL function返回多行

1. 建表

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 

2. 返回单字段的多行（returns setof datatype）

• 不指定out参数，使用return next xx：

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;

• 指定out参数，使用return next：

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;

• 使用return query：

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;

3. 返回多列的多行（returns setog record）

• 不指定out参数，使用return next xx：

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;

在使用func04的时候注意，碰到问题列下：

问题一：

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

解决：（来自： https://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions）

　 　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,…);

问题二：

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

解决: （来自： http://stackoverflow.com/questions/8605174/postgresql-error-42601-a-column-definition-list-is-required-for-functions-ret
http://francs3.blog.163.com/blog/static/4057672720125232216773/）

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)

这个问题在func04如果指定out参数就不会有问题，如下func05所示：

• 指定out参数，使用return next：

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)

• 使用return query：

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)