The PL/pgSQL "get diagnostics" statement

Syntax

• Grammar
• Diagram

plpgsql_get_diagnostics_stmt ::= GET [ CURRENT ] DIAGNOSTICS 
                                 plpgsql_diagnostics_item [ , ... ]

plpgsql_diagnostics_item ::= { variable | formal_arg } { := | = } 
                             plpgsql_diagnostics_item_name

plpgsql_diagnostics_item_name ::= PG_CONTEXT | ROW_COUNT | RESULT_OID

Semantics

The PL/pgSQL get diagnostics statement is typically used, for tracing, during the development process. It's likely, here, that you'd use raise info to display the information that it returns.

The get diagnostics syntax specifies three separate run-time facts—all or some of which can, optionally, be read at once:

• pg_context: This reports the subprogram call stack from the top-level server call through the execution of the get diagnostics statement itself.
• row_count: This reports the number of rows processed by the most recent SQL DML statement invoked by the subprogram or do statement from whose code get diagnostics is invoked. (DML statements invoked from code that the present subprogram or do statement might invoke are not considered.)
• result_oid: This is useful only after an insert statement, (nominally) the OID of the most-recently inserted row.

Typically, get diagnostics will be invoked in the executable section. But it might be usefully invoked in a handler in the exception if this executes a DML statement.

Further information about the outcome of the most recent SQL DML statement, that a subprogram or do statement has processed, is provided by the special, implicitly declared and automatically populated, boolean variable found. This variable is always false when evaluated in the declaration section, or by the first statement in the executable section, in the top-level block statement of the subprogram or do statement that issues the DML statements. Later, it will be true if the most recent SQL DML statement affected at least one row. (Just like the value that get diagnostics returns for row_count, the value of found is not affected by DML statements issued from code that the present subprogram or do statement might invoke.)

Example

First, create the function s.f3() that inserts some rows into the table s.t and then invokes get diagnostics. It returns a value of the user-defined composite type s.diagnostics. This has three attributes:

• found: for the value of the special variable found
• rows: for the value from the get diagnostics invocation's row_count
• ctx: for the value from the get diagnostics invocation's pg_context

\c :db :u
drop schema if exists s cascade;
create schema s;
create type s.diagnostics as(found boolean, rows bigint, ctx text);
create table s.t(k int primary key, v int not null);

create function s.f3(lo in int, hi in int)
  returns s.diagnostics
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  diags s.diagnostics;
begin
  insert into s.t(k, v)
  select a.v, a.v*10 from generate_series(lo, hi) as a(v);

  diags.found := found;
  get diagnostics
    diags.rows := row_count,
    diags.ctx  := pg_context;
  return diags;
end;
$body$;

Now create the function s.f2() that invokes s.f3() and returns the value of s.diagnostics that s.f3() returns. And, in the same way, create the function s.f1() that invokes s.f2() and returns the value of s.diagnostics that s.f2() returns. The purpose of these two functions is simply to ensure that there's a call stack of some depth above the invocation of get diagnostics so as best to demonstrate the usefulness of the returned value of pg_context.

create function s.f2(n in int)
  returns s.diagnostics
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  lo constant int not null := 7;
  hi constant int not null := lo + n - 1;
begin
  return s.f3(lo, hi);
end;
$body$;

create function s.f1()
  returns s.diagnostics
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
begin
  return s.f2(5);
end;
$body$;

Finally, create the table function s.f0() that invokes s.f1() and displays the values of the attributes of s.diagnostics that are seen in s.f3():

create function s.f0()
  returns table(z text)
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  diags  s.diagnostics;
  rows   bigint not null := 0;
begin
  -- s.f1() invokes s.f2().
  -- s.f2() invokes s.f3()
  -- s.f3() does the "insert".
  diags := s.f1();
  
  -- Sanity checks.
  get diagnostics rows := row_count;
  assert rows = 0;
  assert not found;

  z := 'diags.found:  '||diags.found::text;                 return next;
  z := 'diags.rows:   '||diags.rows::text;                  return next;
  z := '';                                                  return next;
  z := 'diags.ctx:    '||chr(10)||diags.ctx::text;          return next;
end;
$body$;

Now test it:

select s.f0() as "Values seen in s.f3().";

This is the result:

                      Values seen in s.f3().                       
-------------------------------------------------------------------
 diags.found:  true
 diags.rows:   5
 
 diags.ctx:                                                       +
 PL/pgSQL function s.f3(integer,integer) line 9 at GET DIAGNOSTICS+
 PL/pgSQL function s.f2(integer) line 6 at RETURN                 +
 PL/pgSQL function s.f1() line 3 at RETURN                        +
 PL/pgSQL function s.f0() line 9 at assignment

You might like to select the rows from s.t as a further sanity check. This is the result:

 k  |  v  
----+-----
  7 |  70
  8 |  80
  9 |  90
 10 | 100
 11 | 110