SETOF RECORD :
select * from employee
-- drop function fn_withouttable()
select * from fn_record_emp() as tbl(empid int,empname varchar)
create or replace function fn_record_emp()
returns SETOF record
language plpgsql
as
$$
declare r1 record; -- empid int; empname varchar;
begin
for r1 in select empcode,empfname from employee
loop
return query select r1.empcode ,r1.empfname;
end loop;
end
$$
RETURN QUERY
select * from fn_withouttable1() as f(empid int,empname varchar)
create or replace function fn_withouttable1()
returns SETOF record
language plpgsql
as
$$
declare r1 record;
begin
return query select empcode,empfname from employee;
end
$$
RETURN TABLE PASSING PARAMETER
select * from cur_returnval()
drop function cur_returnval()
create or replace function cur_returnval()
returns table (empid int,empname varchar,empsalary int)
language plpgsql
as
$$
declare c1 cursor for select * from employee;
r1 record; -- empid int;empname varchar;empsalary int;
begin
open c1;
loop
fetch c1 into r1 ; exit when not found;
empid := r1.empcode;
empname :=r1.empfname;
empsalary := r1.salary;
return next;
end loop;
close c1;
end
$$
RETURN TABLE VALUE USING CURSOR
do
$$
declare c1 cursor for select * from employee ;
r1 record ;
begin
open c1 ;
loop
fetch c1 into r1; exit when not found ;
raise notice '% % %',r1.empcode,r1.empfname,r1.salary;
end loop;
close c1;
end
$$
Return_table_with_for_loop
select * from fn_returnloop()
create or replace function fn_returnloop()
returns table (empid int,empname varchar,e_salary int)
language plpgsql
as
$$
declare r1 record;
begin
for r1 in select * from employee
loop
empid := r1.empcode ;empname :=r1.empfname ;e_salary :=r1.salary;
return next;
end loop;
end
$$
-----------------------Return Table--------------------------
select * from fn_returnvalue('IT')
create or replace function fn_returnvalue(dept varchar)
returns table (empid int,empname varchar,e_salary int)
language plpgsql
as
$$
begin
return query select empcode,empfname,salary from employee where job=dept;
end
$$
-----------------------INOUT---------------------------------
select * from fn_inout(10,20)
create or replace function fn_inout(inout x int,inout y int)
language plpgsql
as
$$
begin
select x,y into y,x ;
end
$$
-------------------------OUT---------------------------
select * from fn_salary_status('ANALYST')
create or replace function fn_salary_status
(dept varchar,out edept varchar,out emax int,out emin int,out eavg int)
language plpgsql
as
$$
begin
select job,max(salary),min(salary),avg(salary) into edept,emax,emin,eavg
from employee where job=dept
group by job ;
end
$$
select job,max(salary),min(salary),avg(salary)-- into edept,emax,emin,eavg
from employee where job='IT'
No comments:
Post a Comment