How to Create And Excute Function With Refcursor in Postgresql

How to Create And Execute Function With Refcursor in Postgresql

Create Two Tables

create table EmployeeDetails(EmployeeId serial primary key,FirstName varchar(60), LastName varchar(60),Place varchar(100),Country varchar(40),PhoneNo varchar(40),password varchar(100)); create table RoomDetails(RoomId serial primary key ,RoomName varchar(100),RoomSize varchar(20))
Insert Values

Insert into RoomDetails(RoomName,RoomSize) values('Abdul kalam','10'), ('aryabhata','15'), ('Nasa','20'), ('vivekananda','10'), ('Rohini','5'); insert into EmployeeDetails(FirstName,LastName,Place,Country,PhoneNo,password) values('Vasanth', 'R','SGRI', 'India','8056512886','hello'), ('Ram', 'S', 'SGRI', 'India', '9366180976','hello'), ('Ravi', 'E', 'SGRI','India', '123456789','hello'), ('Krishna','R', 'SGRI', 'India', '987654321','hello'), ('Divya', 'R', 'SGRI', 'India', '7502036282','hello');

Create a Required Function

  CREATE OR REPLACE FUNCTION show_multiple(ref1 refcursor,ref2 refcursor) RETURNS SETOF refcursor AS $$                          

    BEGIN

      OPEN ref1 FOR SELECT * FROM EmployeeDetails;   

      RETURN NEXT ref1;                              -- Return the cursor to the caller

      OPEN ref2 FOR SELECT * FROM RoomDetails;  

      RETURN NEXT ref2;                            -- Return the cursor to the caller

    END;

    $$ LANGUAGE plpgsql;

Then Unchceck Auto commit

Run this one

select * from show_multiple('ref1','ref2');

Then this

FETCH ALL IN "ref1";

Finally this

FETCH ALL IN "ref2";

If you want to close transaction use commit

commit;

Then Again check the auto commit for normal transactions