postgresqldatabase-migrationamazon-auroraora2pg

Postgres alternatives for Oracle DBMS_UTILITY.FORMAT_CALL_STACK and DBMS_UTILITY.FORMAT_ERROR_STACK


What are the equal/closest alternatives in Postgres for Oracle DBMS_UTILITY.FORMAT_CALL_STACK and DBMS_UTILITY.FORMAT_ERROR_STACK ?


Solution

  • You can use GET DIAGNOSTICS or GET STACKED DIAGNOSTICS statement and read a PG_CONTEXT field.

    CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$
    BEGIN
      RETURN inner_func();
    END;
    $$ LANGUAGE plpgsql;
    
    CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$
    DECLARE
      stack text;
    BEGIN
      GET DIAGNOSTICS stack = PG_CONTEXT;
      RAISE NOTICE E'--- Call Stack ---\n%', stack;
      RETURN 1;
    END;
    $$ LANGUAGE plpgsql;
    
    SELECT outer_func();
    
    NOTICE:  --- Call Stack ---
    PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS
    PL/pgSQL function outer_func() line 3 at RETURN
    CONTEXT:  PL/pgSQL function outer_func() line 3 at RETURN
    outer_func
    ------------
            1
    (1 row)