returning a refcursor with error code and message from postgres function

Question

I have a postgres function that returns a refcursor :

CREATE OR REPLACE FUNCTION ngfcst.meta_user_preference_error_test_go(--need to add -- inout o_errcode varchar, inout o_errmsg varchar--
    i_login character varying,
    i_pref_type character varying DEFAULT NULL::character varying)
    RETURNS refcursor
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
ref refcursor='o_user_pref_cur'; -- Declare a cursor variable
declare
    err_context text;
BEGIN

if i_pref_type is NULL THEN
    OPEN ref FOR 
    select * from ngfcst.ORG_USER_PREFERENCE where login=i_login;
else
    OPEN ref FOR
    select * from ngfcst.ORG_USER_PREFERENCE oup where oup.login=i_login and oup.pref_type=i_pref_type;
end if;

RETURN ref; -- Return the cursor to the caller
exception
 when others then
        GET STACKED DIAGNOSTICS err_context = PG_EXCEPTION_CONTEXT;
        RAISE WARNING 'Error Name:%',SQLERRM;
        RAISE WARNING 'Error State:%', SQLSTATE;
        RAISE WARNING 'Error Context:%', err_context;
        CALL ngfcst.ins_error_logs( SQLSTATE,  err_context, '','','ngfcst.meta_user_preference_error_test');
       return -1;
        
        
END;
$BODY$;

now i am able to get any error through the exception block, but my requirement is to return the error code and error message in case we encounter any error. this is to be done by 2 extra out params o_errcode and o_errmsg.

when i try this i get the error

ERROR:  function result type must be record because of OUT parameters
SQL state: 42P13

how do i get the cursor in case of successful query and the error message and error code in case of any exception.

i need to implement this so that the calling block gets an error code <> 0 in case of any error and i immediately stop the process and log the error (which i am doing through ins_error_logs procedure)

Answer 1

Use three OUT parameters. An example:

CREATE FUNCTION meta_user_preference_error_test_go(
   IN  i_login     text,
   OUT o_errcode   text,
   OUT o_errmsg    text,
   OUT o_cursor    refcursor,
   IN  i_pref_type text DEFAULT NULL
) RETURNS record LANGUAGE plpgsql
AS $$BEGIN
   o_errcode := '0';
   o_errmsg  := 'SUCCESS';
   o_cursor  := 'o_cursor';
   OPEN o_cursor FOR SELECT 42;
END;$$;

BEGIN;

SELECT * FROM meta_user_preference_error_test_go('login');

 o_errcode | o_errmsg | o_cursor 
-----------+----------+----------
 0         | SUCCESS  | o_cursor
(1 row)

FETCH ALL FROM o_cursor;

 ?column? 
----------
       42
(1 row)

COMMIT;