How does one report the line on which an error occured in Postgres/plpgsql?

Question

I have been using something more or less like this in postgres to emulate how, in SQL Server, I have used Try/Catch blocks with transactions that can be rolled back in the Catch if an error is found:

do $$
begin
[SQL here]

exception when others then
    raise notice 'Error in insert statement ---> % %', SQLERRM, SQLSTATE LINE;
end;    

$$ language 'plpgsql';

Is there a way to report the line where the error occurred, like "ERROR_LINE()?

Thanks in advance

Answer 1

On some modern PostgreSQL, you can use GET STACKED DIAGNOSTICS statement. There is not possibility to get linenumber, but you can get a call context, where lineno is included:

postgres=> DO $$
DECLARE 
  a int DEFAULT 0;
  _c text;
BEGIN
  BEGIN
    PERFORM 10/a;
  EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS _c = PG_EXCEPTION_CONTEXT;
    RAISE NOTICE 'context: >>%<<', _c;
  END;
END;
$$;
NOTICE:  00000: context: >>SQL statement "SELECT 10/a"
PL/pgSQL function inline_code_block line 7 at PERFORM<<
LOCATION:  exec_stmt_raise, pl_exec.c:3041
DO