CODEWITHSUNDEEP
postgresql
kiran kumar
kiran kumar

Reputation: 21

How to get multiple result set from stored procedure in PostgreSQL 12.5 version?

Table creation

CREATE TABLE schname.newtable ( id serial NOT NULL, 
    username varchar(100) NULL,
    inserted_on varchar NULL DEFAULT now() );

Procedure cretaion

CREATE OR REPLACE PROCEDURE schname.pr_insertion(p_type integer, p_name character varying, INOUT p_cur refcursor DEFAULT 'kkk'::refcursor)
 LANGUAGE plpgsql
AS $procedure$
declare  
    err_context text;
begin 
    begin 

if p_type = 1 then   
    insert into schname.newtable (username) values (p_name) ;

    open p_cur for select  id, username, inserted_on FROM schname.newtable ;
end if ;  

exception  
    when others then
    rollback;
        GET STACKED DIAGNOSTICS err_context = PG_EXCEPTION_CONTEXT;
        RAISE INFO 'Error Name:%',SQLERRM;
        RAISE INFO 'Error State:%', SQLSTATE;
        RAISE INFO 'Error Context:%', err_context;

       insert into schname.lms_proc_error_log (function_name, ptype, module1, error_number, error_state, error_context)
       select 'pr_insertion', p_type, 'Login-check', cast(sqlerrm as varchar) , cast(sqlstate as varchar)  , err_context ;  

        open p_cur for 
        select sqlerrm || ' fail' || err_context as status ;

end; 
  commit; 

end ;
$procedure$
;

Getting the following error While executing/calling the above stored procedure in query window

 call schname.pr_insertion(1, 'h') ;
 FETCH ALL FROM "kkk" ;

ERROR: SQL Error [34000]: ERROR: cursor "kkk" does not exist

Note: If I run below query the record is being inserting, but we are unable to fetch the result set

 call schname.pr_insertion(1, 'h') ;
    FETCH ALL FROM "kkk" ;

Upvotes: 1

Views: 55

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246093

Cursors exist only for the duration of a transaction, so you have to wrap both statements in a transaction:

BEGIN;
CALL schname.pr_insertion(1, 'h');
FETCH ALL FROM kkk;
COMMIT;

Besides, it is not a smart idea to use the same cursor for an error message like you are doing. Use a function and make the message the function result, or use another INOUT parameter for the message.

Upvotes: 1

Related Questions