반응형

/********************************************************************************************
-- 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();


반응형

+ Recent posts