/********************************************************************************************
-- Title : [PGS9.2] 테이블/레코드 출력 사용자 정의 함수
-- Reference : http://www.postgresql.org/docs/9.2/static/plpgsql-control-structures.html
-- Key word : postgresql table record return function create function 함수
********************************************************************************************/
-- 초기화
drop function fn_getallfoo();
drop function fn_ttt();
drop function fn_ttt2();
drop table foo;
drop table foo2;
-- 테이블 생성 및 데이터 입력
create table foo (fooid int, foosubid int, fooname varchar(20));
create table foo2 (fooid int, price int);
insert into foo values (1, 2, 'three');
insert into foo values (4, 5, 'six');
insert into foo values (6, 7, 'seven');
insert into foo2 values (1, 100);
insert into foo2 values (4, 200);
insert into foo2 values (6, 300);
select * from foo a, foo2 b
where a.fooid = b.fooid;
-- 함수 생성
-- language='plpgsql'/setof {table}
create or replace function fn_getallfoo()
returns setof foo as
$$
declare
r foo%rowtype;
begin
for r in (select * from foo
where fooid > 0
)
loop
-- can do some processing here
return next r; -- return current row of select
end loop;
return;
end
$$ language 'plpgsql';
select * from fn_getallfoo();
-- 함수 생성
-- language=sql/setof {table}
create or replace function fn_ttt()
returns setof foo as
$$
select * from foo;
$$ language sql;
select * from fn_ttt();
-- 함수 생성
-- language=sql/setof record
-- drop function fn_ttt2(); /*뭐 때문인지 drop/create 하라네!*/
create or replace function fn_ttt2
(out fooid integer, out foosubid int, out fooname text, out price int)
returns setof record as
$$
select a.fooid, a.foosubid, a.fooname, b.price
from foo a, foo2 b
where a.fooid = b.fooid;
$$ language sql;
select * from fn_ttt2();