---
title: "The PL/pgSQL \"get diagnostics\" statement"
url: "https://docs.yugabyte.com/stable/api/ysql/user-defined-subprograms-and-anon-blocks/language-plpgsql-subprograms/plpgsql-syntax-and-semantics/executable-section/basic-statements/get-diagnostics/"
---

# The PL/pgSQL "get diagnostics" statement

Describes the syntax and semantics of the PL/pgSQL "get diagnostics" statement. [YSQL].

## Syntax

- [![Grammar Icon](/icons/file-lines.svg)Grammar](#grammar-1)
- [![Diagram Icon](/icons/diagram.svg)Diagram](#diagram-1)

```output.ebnf
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
```

#### plpgsql\_get\_diagnostics\_stmt

GETCURRENTDIAGNOSTICS,[plpgsql\_diagnostics\_item](#plpgsql-diagnostics-item)

#### plpgsql\_diagnostics\_item

[variable](/stable/api/ysql/user-defined-subprograms-and-anon-blocks/language-plpgsql-subprograms/plpgsql-syntax-and-semantics/executable-section/basic-statements/get-diagnostics/syntax_resources/grammar_diagrams#variable)[formal\_arg](/stable/api/ysql/user-defined-subprograms-and-anon-blocks/language-plpgsql-subprograms/plpgsql-syntax-and-semantics/executable-section/basic-statements/get-diagnostics/syntax_resources/grammar_diagrams#formal-arg):==[plpgsql\_diagnostics\_item\_name](#plpgsql-diagnostics-item-name)

#### plpgsql\_diagnostics\_item\_name

PG\_CONTEXTROW\_COUNTRESULT\_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.

The returned value for 'result\_oid' is always zero.

The syntax is accepted; but the returned value for the *result\_oid* item is always zero. The PostgreSQL documentation says that you get a non-zero result only when the *create table* statement for target table for the insert uses the *with oids* clause. But YSQL does not support this. (The attempt causes the *0A000* error: *"OIDs are not supported for user tables."*)

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*

```plpgsql
\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*.

```plpgsql
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()*:

```plpgsql
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:

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

This is the result:

```output
                      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:

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