technical skills grow

Responsive Ads Here

Wednesday, December 8, 2021

Return values using Table , Record ,SETOF ,INOUT ,OUT in postgresql

 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

Powered by Blogger.

Labels

Contact Form

Name

Email *

Message *

Search This Blog

Blog Archive

Ad Code

Responsive Advertisement

Recent Posts