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 setofdatatype)

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;

func 04を使用するときに注意して、問題に遭遇した列の下:問題1:

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, arg2, …);
問題2:

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)

この問題はfunc 04でoutパラメータを指定すれば問題ありません.以下func 05に示します.

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)