설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


총 게시물 91건, 최근 1 건
   

멀티 컬럼 반환하는 함수 만들기

글쓴이 : PostgresDBA 날짜 : 2014-12-19 (금) 11:08 조회 : 17479
I. 순수 SQL 만 사용하는 함수라면.
SQL> select * from emp;
 empno | ename  |    job    |  mgr   |  hiredate  | sal  |  comm  | deptno 
-------+--------+-----------+--------+------------+------+--------+--------
  7839 | KING   | PRESIDENT | <NULL> | 1981-11-17 | 5000 | <NULL> |     10
  7844 | TURNER | SALESMAN  |   7698 | 1981-09-08 | 1500 |      0 |     30
  7876 | ADAMS  | CLERK     |   7788 | 1983-01-12 | 1100 | <NULL> |     20
  7900 | JAMES  | CLERK     |   7698 | 1981-12-03 |  950 | <NULL> |     30
  7902 | FORD   | ANALYST   |   7566 | 1981-12-03 | 3000 | <NULL> |     20
  7934 | MILLER | CLERK     |   7782 | 1982-01-23 | 1300 | <NULL> |     10
(6 rows)

SQL> create or replace function foo(IN _empno integer, out ename text, out job text)
returns setof record as

$$
select ename, job from emp where empno=_empno;
$$

LANGUAGE SQL;
scott@[local]:5432:scottdb] 
SQL> select * from foo(7839);
 ename |    job    
-------+-----------
 KING  | PRESIDENT
(1 row)

scott@[local]:5432:scottdb] 
SQL> 

II. PLSQL 조건절등을 사용한다면..
create or replace function foo2(_empno integer) 
returns table(ename varchar, job varchar) 
as
$$
begin
if _empno = 0 then 
  _empno := 7839; 
end if; 

return query 
select x.ename, x.job  from emp as x where empno=_empno; 
end;
$$ 
LANGUAGE PLPGSQL

select * from foo2(0);
 ename |    job    
-------+-----------
 KING  | PRESIDENT
(1 row)



PostgresDBA 2017-09-26 (화) 16:21
* another sample

create or replace function foo(IN _empno integer)
returns table(
team_mng_cd varchar,
part_mng_cd varchar
)
as
$$
drop table if exists t_zzz;
create temporary table t_zzz as select '1'::text x, '2'::text as y;
select '1'::varchar,'2'::varchar;
$$
LANGUAGE SQL;

select * from foo(1);

create or replace function foo2(IN _empno integer)
returns table(
team_mng_cd varchar,
part_mng_cd varchar
)
as
$$
begin
drop table if exists t_zzz;
create temporary table t_zzz as select '1'::text x, '2'::text as y;
return query select '1'::varchar,'2'::varchar;
end;
$$
LANGUAGE plpgSQL;

select * from foo2(1);
댓글주소
   

postgresdba.com