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)
