postgreSQL : oracle sqlerrm equivalent of postgres

Question

I'm new to PostgreSQL. I have experience in oracle. In oracle , to find the exact error, I use code 'dbms_output.put_line(sqlerrm)' . Here I have a postgresql function returning an integer value

CREATE OR REPLACE FUNCTION public.fn_sqltest(
  p_id integer)
  RETURNS integer
  LANGUAGE 'plpgsql'

  COST 100
  VOLATILE 

 AS $BODY$ 
 Declare
   n integer;
 begin  

     select off_id into n from office 
       where per_id=p_id;

     return n ;

 exception when others then

     return -1;

 end;

 $BODY$;

 ALTER FUNCTION public.fn_sqltest(character varying)
  OWNER TO postgres;

I call this function as below

DO $$ 

DECLARE
  ae integer;

BEGIN 

  ae:=fn_sqltest(10);
  RAISE NOTICE 'exception: % %  ', sqlstate ,  sqlerrm ;
  RAISE NOTICE 'Return value is: % ', ae;

END $$;

and I get the error

ERROR: column "sqlstate" does not exist

How can I show the exact error message like sqlerrm in oracle.

Answer 1

I updated the function and wrote a code in function exception section

CREATE OR REPLACE FUNCTION public.fn_sqltest(
p_id integer)
RETURNS integer
LANGUAGE 'plpgsql'

COST 100
VOLATILE 

 AS $BODY$ 
 Declare
   n integer;
   text_var1 text;
   text_var2 text;
   text_var3 text;
 begin  

   select off_id into n from office 
     where per_id=p_id;

   return n ;

 exception when others then

   GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                      text_var2 = PG_EXCEPTION_DETAIL,
                      text_var3 = PG_EXCEPTION_HINT;

   RAISE NOTICE 'Return value is: %  % %',text_var1 , text_var2, text_var3;

   return -1;

end;

$BODY$;

ALTER FUNCTION public.fn_sqltest(character varying)
OWNER TO postgres;

Another method is by changing the return type. I changed the return type to text and rewrite the exception section code as below

return sqlerrm;

Answer 2

Unlike PL/SQL, in PL/pgSQL SQLSTATE and SQLERRM are not defined outside an exception handler. See the documentation, section "Obtaining Information About An Error".

This also means that you can't get SQLSTATE and SQLERRM of a successful operation, unlike PL/SQL.

So, if you want to use these special variables outside an exception handler, you have to store them in variables.

I don't know how would you like to return them from the function. I can demonstrate this idea inside your function code:

CREATE OR REPLACE FUNCTION public.fn_sqltest(
  p_id integer)
  RETURNS integer
  LANGUAGE 'plpgsql'

  COST 100
  VOLATILE 

 AS $BODY$ 
 Declare
   n integer;
   v_sqlerrm text;
   v_sqlstate text;
begin
 begin  

     select off_id into n from office 
       where per_id=p_id;

     return n ;

 exception when others then
    
     v_sqlerrm := sqlerrm;
     v_sqlstate := sqlstate;
      
 end;

 RAISE NOTICE 'exception: % %  ', v_sqlstate ,  v_sqlerrm ;

 return -1;

end;

 $BODY$;

Answer 3

I know this is old but just for the record: The easiest way to output the state and message of error is to raise them from within the exception clause. You don't need to declare extra variables.

CREATE OR REPLACE FUNCTION fn_sqltest(p_id integer)
    RETURNS integer
AS $$ 
DECLARE
    n   integer;
BEGIN
    SELECT off_id
        INTO n
        FROM office
        WHERE per_id = p_id;
    RETURN n;

EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE 'exception: % - %', SQLSTATE, SQLERRM;
    RETURN -1;
END;
$$ LANGUAGE plpgsql;