CODEWITHSUNDEEP
postgresqlplpgsql
Vape
Vape

Reputation: 130

How to print error message and line number in Postgres like DBMS_UTILITY.FORMAT_ERROR_BACKTRACE is on Oracle

I come from Oracle database to Postgres. What I'm looking for is how to print the error message and the line number when the error occurred in PL/pgSQL? In PL/SQL there is a DBMS_UTILITY.FORMAT_ERROR_BACKTRACE command for that.

Upvotes: 8

Views: 10463

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

A standard option in Postgres is the GET STACKED DIAGNOSTICS with PG_EXCEPTION_CONTEXT

Here's an example

DO $$
DECLARE 
  l_num integer;
  l_context text;
BEGIN
  BEGIN
    l_num := 'TEXT'; --Assign a text to integer
  EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS l_context = PG_EXCEPTION_CONTEXT;
    RAISE NOTICE 'ERROR:%', l_context;
  END;
END;
$$;

Mesg

NOTICE:  ERROR:PL/pgSQL function inline_code_block line 7 at assignment
DO

Upvotes: 10

Related Questions