The PL/pgSQL "raise" statement

Syntax

• Grammar
• Diagram

plpgsql_raise_stmt ::= RAISE [ plpgsql_raise_level ] 
                       [ plpgsql_raise_shortcut_for_exception_or_message ] 
                        [ USING plpgsql_raise_using_item [ , ... ] ]

plpgsql_raise_level ::= DEBUG
                        | LOG
                        | NOTICE
                        | WARNING
                        | EXCEPTION
                        | INFO

plpgsql_raise_shortcut_for_exception_or_message ::= SQLSTATE 
                                                    errcode_literal
                                                    | exception_name
                                                    | message_literal 
                                                      [ text_expression 
                                                        [ , ... ] ]

plpgsql_raise_using_item ::= { ERRCODE
                               | MESSAGE
                               | DETAIL
                               | HINT
                               | SCHEMA
                               | TABLE
                               | COLUMN
                               | DATATYPE
                               | CONSTRAINT } { := | = } 
                             text_expression

Semantics

The PL/pgSQL raise statement has three distinct purposes:

• First, to re-raise the exception that the handler caught (legal only in an exception handler).

• Second, to raise a specified exception and optionally to specify one, some, or all of the various texts that might annotate it:

  • the main message
  • the hint
  • the detail
  • the schema, table, column and data type, where applicable, of the object upon which the erroring operation was attempted
  • the constraint, where applicable, that the erroring operation violated.

• Third, to render a message at the specified severity level to stderr or to the server log file.

The first purpose is met by the bare raise statement. The plpgsql_raise_level rule decides between the second purpose (when the level is exception) and the third purpose (when the level is any legal keyword except for exception).

With one restriction, you can set the same fields for the raise rendered message as you can observe with the get stacked diagnostics statement. The restriction is that you cannot set the exception context. This is because the context is determined programatically—and reflects information, like the call stack, that emerges only at run time. It wouldn't make sense, therefore, to think that the raise statement might set this.

Notice that, though it is legal to set all possible kinds of annotation text for the third purpose, only the main message makes sense.

The legal keywords that the plpgsql_raise_using_item rule specifies correspond, exactly one-to-one, to the legal keywords for the plpgsql_stacked_diagnostics_item_name rule. This extended version of the table that's included in the section How to get information about the error shows the correspondence:

The message is rendered, optionally, in two ways:

• It is rendered, or not rendered, on stderr on the node where the client program is running according to the level used in the raise statement together with the current value of the client_min_messages run-time parameter.

• It is rendered, or not rendered, in the server log file according to the level used in the raise statement and the current value of the log_min_messages configuration parameter.

Any two sessions can each set different values for each of the two *min_messages parameters. The outcome is easy to understand when the report text is rendered on stderr where the client application runs. But it takes more effort to understand, for two reasons, when the report text is rendered on the server log file:

• Messages from different concurrent clients simply interleave in the single server log file on a particular node.
• YugabyteDB is typically deployed using three or more nodes—and, especially when a load balancer is used, it might not be easy to identify which node is hosting the Postgres server process for the client of interest.

The discussion in this section will therefore be limited to the messages that are rendered on stderr and that are controlled by the client_min_messages parameter.

How to specify whether or not the "raise" report text is rendered on "stderr"

You can see the allowed values for client_min_messages by attempting to set it to, say, illegal. You get an error, of course. And the hint lists the available values. However, empirical testing shows that the list of values is wrong in that it omits info and bare debug. This:

set client_min_messages = info;

and this:

set client_min_messages = debug;

each runs without error. For completeness, the missing info is added at the end of the list here—and the missing bare debug is added at the start. The order of the values in the list is significant. Here it is:

• debug, debug5, debug4, debug3, debug2, debug1, log, notice, warning, error, info

Notice how the spellings of the allowed values correspond, but only approximately, to the list of legal level keywords for the raise statement.

• The value error corresponds to the raise statement's level keyword exception.
• The values (bare) debug, debug1, debug2, debug3, debug4, and debug5 all correspond to the raise statement's level keyword debug.

These differences reflect the fact that the internal implementation of top-level SQL statements can also generate messages—and that the SQL message levels are more nuanced than for those that the PL/pgSQL raise statement can specify.

The parameter name, client_min_messages, includes an abbreviation for "minimum"—and this implies that it's appropriate to say that the allowed values are listed in order of increasing priority where (bare) debug has the lowest priority. (The allowed values info and error tie with each other; each has the highest priority.) Here is the rule that determines whether the report text that the raise statement specifies is rendered or not:

• A message whose level corresponds to the current setting of client_min_messages, or to any allowed value that has a higher priority than the current setting, will be rendered.
• Conversely, a message whose level corresponds to any allowed value that has a lower priority than the current setting of client_min_messages, will not be rendered.
• As a special case, messages with the level exception or info are always rendered. (This is why info is written, above, at the end of the list.)

set client_min_messages = warning;

do $body$
begin
  raise log '"drop table if exists" invoked';
  drop table if exists pg_temp.t;
  raise log '"create table" invoked';
  create table pg_temp.t(n int);
  raise log '"drop table" invoked';
  drop table pg_temp.t;
end;
$body$;

LOG:  "drop table if exists" invoked
NOTICE:  table "t" does not exist, skipping
LOG:  "create table" invoked
LOG:  "drop table" invoked

set client_min_messages = debug;

DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, ybDataSent: N, ybDataSentForCurrQuery: N, xid/subid/cid: 0/1/0
LOG:  "drop table if exists" invoked
DEBUG:  Resetting read point for statement in Read Committed txn
DEBUG:  relation "pg_temp.t" does not exist
NOTICE:  table "t" does not exist, skipping
LOG:  "create table" invoked
DEBUG:  Resetting read point for statement in Read Committed txn
LOG:  "drop table" invoked
DEBUG:  Resetting read point for statement in Read Committed txn
DEBUG:  drop auto-cascades to type t
DEBUG:  drop auto-cascades to type t[]
DEBUG:  CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, ybDataSent: Y, ybDataSentForCurrQuery: Y, xid/subid/cid: 0/1/4

\c :db :u
drop schema if exists utils cascade;
create schema utils;

create procedure utils.trace(msg in text)
  language plpgsql
  set search_path = pg_catalog, pg_temp
as $body$
declare
  trace_on boolean not null := false;
begin
  <<evaluate_trace_on>>begin
    trace_on = lower(current_setting('trace.mode')) = 'true';
  exception when undefined_object then
    declare
      err_msg text not null := '';
    begin
      get stacked diagnostics err_msg := message_text;
      assert err_msg = 'unrecognized configuration parameter "trace.mode"';
    end;
  end evaluate_trace_on;
  if trace_on then
    raise info '%', msg;
  end if;
end;
$body$;

trace_on = lower(current_setting('trace.mode')) = 'true';

drop schema if exists s cascade;
create schema s;

create procedure s.p1()
  language plpgsql
  set search_path = pg_catalog, pg_temp
as $body$
begin
  call utils.trace('"drop table if exists" invoked');
  drop table if exists pg_temp.t;
  call utils.trace('"create table" invoked');
  create table pg_temp.t(n int);
  call utils.trace('"drop table" invoked');
  drop table pg_temp.t;
end;
$body$;

\c :db :u
set client_min_messages = warning;
call s.p1();

set trace.mode = true;
call s.p1();

INFO:  "drop table if exists" invoked
INFO:  "create table" invoked
INFO:  "drop table" invoked

set trace.mode = 42;
call s.p1();

create procedure utils.trace(kind in text, msg in text)
  language plpgsql
  set search_path = pg_catalog, pg_temp
as $body$
declare
  param_val text not null := '';
  options text[] not null := array[''];
begin
  begin
    param_val := lower(current_setting('trace.kinds'));
    options := string_to_array(replace(param_val, ' ', ''), ',');
  exception when undefined_object then
    declare
      err_msg text not null := '';
    begin
      get stacked diagnostics err_msg := message_text;
      assert err_msg = 'unrecognized configuration parameter "trace.kinds"';
    end;
  end;

  if array[lower(kind)] <@ options then
    raise info '%', msg;
  end if;
end;
$body$;

create procedure s.p2()
  language plpgsql
  set search_path = pg_catalog, pg_temp
as $body$
begin
  call utils.trace('red',    'Red message.');
  call utils.trace('blue',   'Blue message.');
  call utils.trace('green',  'Green message.');
  call utils.trace('yellow', 'Yellow message.');
end;
$body$;

\c :db :u
set client_min_messages = warning;
call s.p2();

set trace.kinds = 'blue';
call s.p2();

INFO:  Blue message.

set trace.kinds = 'Green';
call s.p2();

INFO:  Green message.

set trace.kinds = 'red,  BLUE,  Yellow';
call s.p2();

INFO:  Red message.
INFO:  Blue message.
INFO:  Yellow message.

set trace.kinds = '';
call s.p2();

The semantically legal variants of the "raise" statement

The raise statement syntax is defined by the mandatory raise keyword followed by three optional clauses:

• plpgsql_raise_level — hereinafter the level clause
• plpgsql_raise_shortcut_for_exception_or_message — hereinafter the shortcut clause
• USING plpgsql_raise_using_item [ , ... ] — hereinafter the using clause

However, semantics rules govern which combinations of these clauses are legal. These are the useful legal combinations:

RAISE;

RAISE [LEVEL] SQLSTATE errcode;
RAISE [LEVEL] exception_name;
RAISE [LEVEL] message_literal [ text_expression [ , ... ] ];

RAISE [LEVEL] USING option { := | = } text_expression [ , ... ];

Notice that this:

do $body$
begin
  raise exception;
end;
$body$;

with any legal level keyword, causes the 42601 syntax error.

The bare raise variant is a singleton. In all the other variants, level is optional. Omitting it has the same effect as writing exception.

These combinations are also legal:

RAISE [LEVEL] SQLSTATE errcode USING option { := | = } text_expression [ , ... ];
RAISE [LEVEL] exception_name USING option { := | = } text_expression [ , ... ];
RAISE [LEVEL] message_literal [ text_expression [ , ... ] ] USING option { := | = } text_expression [ , ... ];

However, these hybrids bring the possibility of specifying the same using option twice. (Notice that the same notion, for historical reasons, has different spellings at different syntax spots. For example, sqlstate and errcode denote the same notion.) Try these three negative tests. First:

do $body$
begin
  raise exception sqlstate 'YB257' using errcode := 'YB257';
end;
$body$;

This causes the 42601 runtime error, "RAISE option already specified: ERRCODE". Second:

do $body$
begin
  raise exception unique_violation using errcode := '23505';
end;
$body$;

This causes the same 42601 runtime error with the same message. And third:

do $body$
begin
  raise info 'Some facts' using message := 'Some facts';
end;
$body$;

This, too, causes the 42601 runtime error but the message here is "42601: RAISE option already specified: MESSAGE".

"Bare raise"

This is the degenerate form of the syntax. Every optional clause is simply omitted, thus:

raise;

It has a special meaning that distinguishes it from all of the other raise variants. You can write it in either the executable section or the exception section without causing a syntax error. But try this:

drop schema if exists s cascade;
create schema s;
create procedure s.p()
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
begin
  raise;
end;
$body$;

call s.p();

Procedure s.p() is created without error. But you get the 0Z002 runtime error:

RAISE without parameters cannot be used outside an exception handler

This reflects the defined semantics of the bare raise statement. When it's invoked in an exception handler, it re-raises the exception (together with the same stacked diagnostics facts) that was caught by the handler. Try this:

drop schema if exists s cascade;
create schema s;
create function s.f()
  returns table(z text)
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
begin
  begin
    perform 1.0/0.0;
  exception when division_by_zero then
    z := '"division_by_zero" caught';                                 return next;
    raise;
  end;
exception when division_by_zero then
  declare
    v_pg_exception_context text not null := '';
  begin
    get stacked diagnostics
      v_pg_exception_context := pg_exception_context;

    z := '"division_by_zero" caught again, following "bare raise"';   return next;
    z := '';                                                          return next;
    z := 'context: '||v_pg_exception_context;                         return next;
  end;
end;
$body$;

select s.f();

This is the result:

 "division_by_zero" caught
 "division_by_zero" caught again, following "bare raise"
 
 context: SQL statement "SELECT 1.0/0.0"                +
 PL/pgSQL function s.f() line 4 at PERFORM

The critical point, here, is that the exception context reports that the error occurred when executing "perform 1.0/0.0;" in the function "s.f()"—and not at the bare raise statement.

"raise info" with the "shortcut" syntax

First, try this counter-example:

drop schema if exists s cascade;
create schema s;

create procedure s.p()
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
begin
      raise info using
        errcode    := 'YB573',
        message    := 'Main info,',
        detail     := 'Info detail.',
        hint       := 'Info hint.',
        schema     := 'n/a',
        table      := 'n/a',
        column     := 'n/a',
        datatype   := 'n/a',
        constraint := 'n/a';
end;
$body$;

\set VERBOSITY default
call s.p();

It produces this output:

INFO:  Main info,
DETAIL:  Info detail.
HINT:  Info hint.

You don't see the other annotation kinds unless you do this:

\set VERBOSITY verbose
call s.p();

Now you see this output:

INFO:  YB573: Main info,
DETAIL:  Info detail.
HINT:  Info hint.
SCHEMA NAME:  n/a
TABLE NAME:  n/a
COLUMN NAME:  n/a
DATATYPE NAME:  n/a
CONSTRAINT NAME:  n/a
LOCATION:  exec_stmt_raise, pl_exec.c:3852

It's certainly meaningless to specify the error code YB573 (even though you get what you asked for) because the code for successful completion of a top-level server call is 00000. You might argue that the other annotation kinds could be useful if you're writing re-usable code for other developers, if there are some legal, but ill-advised uses, and if you want to issue a warning when you detect such a case. (Notice that, by definition, only an exception interrupts normal execution.) This general syntax variant:

RAISE [LEVEL] USING option { := | = } text_expression [ , ... ];

gives you what you need for this use. However, the overwhelmingly common case for raise with all legal options for level except for exception is to generate only the main message and to convey all of the facts that you intend in that single text. The general syntax then reduces to this:

drop schema if exists s cascade;
create schema s;

create procedure s.p()
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  template constant text not null := 'Here are two interesting values: "%s"; and "%s".';
begin
  raise info using message := format(template, 'red', 'blue');
end;
$body$;

\set VERBOSITY default
call s.p();

The code takes advantage of the fact that the argument for each using clause option is a text expression. It produces this output:

INFO:  Here are two interesting values: "red"; and "blue".

Compare the executable section with the version that uses the shortcut syntax:

begin
  raise info 'Here are two interesting values: "%"; and "%".', 'red', 'blue';
end;

This is less cluttered than the general syntax. And, of course, it produces the identical output. (Notice, though, that the special form of the value substitution syntax is less expressive than the general substitution syntax that the format() function supports.) Nevertheless, the shortcut syntax is typically preferred for the raise info use-case.

"raise exception" with the "shortcut" syntax

Consider a system of user-defined subprograms where the call stack can grow to a significant depth and where a subprogram can suffer an expected, but regrettable, error at the deepest level in the stack. Suppose that the error can be detected only in that deeply-nested subprogram invocation. Suppose, further, that an alternative viable approach that calls a different starting subprogram from the top-of-stack subprogram is available.

Of course it's possible to ensure that the design of every subprogram can notice such an outcome, abort its own execution, and communicate the failure to its callee using dedicated formal status arguments—all the way up the stack. But this approach leads to cluttered code and it's hard to follow the paradigm consistently. Some languages have a native feature called long jump, or similar which would be preferred in the present scenario. But PL/pgSQL has no such feature. However raising an exception down in the depth and handling it at top level provides a sound alternative solution. The only requirement is that a dedicated handler can be written for just this scenario—and only this.

The following code provides a minimal, but sufficient, illustration of this approach. First, create the procedure that implements the preferred approach but that might fail in a predictable way.

\c :db :u
drop schema if exists s cascade;
create schema s;

create procedure s.preferred(cause_preferred_failure in boolean)
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
begin
  case cause_preferred_failure
    when true then
      raise info 'Regrettable but not unexpected: preferred() failed.';
      raise sqlstate 'YB257';
    else
      raise info 'preferred() succeeded.';
  end case;
end;
$body$;

Look at the table of Error Codes and Condition Names in the PostgreSQL documentation. It has about (but a little fewer than) 250 entries. An error code must be a mixture of exactly five digits or upper-case Latin characters. (Try raise sqlstate '93a57'. It causes the 42601 syntax error: "invalid SQLSTATE code".) This means that there are (10 + 26)^5 distinct possible values. Subtracting 250 for the values that PostgreSQL reserves means that there are still more than 60 million available values. You'll find it easy, therefore, to invent new codes for your development shop and to maintain a list of these, each with its purpose. For example, search in the page for YB257. It isn't found.

Next create the fallback procedure. Assume that it's, for example, significantly slower than preferred() but that any failure it might suffer is unexpected:

create procedure s.fallback(cause_fallback_failure in boolean)
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
begin
  case cause_fallback_failure
    when true then
      -- Simulate ordinary, but unexpected, SQL error
      raise division_by_zero;
    else
      raise info 'fallback() succeeded.';
  end case;
end;
$body$;

Finally, create the procedure caller(). This simulates the subprogram that the client application invokes. In the real code, the execution point will move through several subprograms as the call stack grows until it reaches the procedure preferred():

create procedure s.caller(
  cause_preferred_failure in boolean = false,
  cause_fallback_failure  in boolean = false)
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
declare
  ctx text not null := '';
  err text not null := '';
begin
  call s.preferred(cause_preferred_failure);
exception
  when sqlstate 'YB257' then
    begin
      call s.fallback(cause_fallback_failure);
    exception
      when others then
        get stacked diagnostics err := returned_sqlstate, ctx := pg_exception_context; 
        raise info 'Unexpected error: %', err;
        raise info '%', chr(10)||ctx;
    end;
  when others then
    -- Here only when preferred() fails in an unexpected way.
    get stacked diagnostics err := returned_sqlstate, ctx := pg_exception_context; 
    raise info 'Unexpected error: %', err;
    raise info '%', chr(10)||ctx;
end;
$body$;

Notice how the procedure fallback() is invoked in the handler for the user-defined error with the dedicated code YB257. Because it's conceivable that the fallback() might fail—even though analysis shows that it shouldn't, a when others handler must be provided for this possibility. This implies using a nested block statement to implement the when sqlstate 'YB257' handler. In this simulation, it simply reports the outcome with raise info. But in a real application, this would be signalled to the client as a failure. One way to do this is always to return an outcome text that represents a JSON value. The flexibility of JSON allows the value to encode:

• EITHER "success" together with any values that such an outcome might be specified to encode
• OR "failure" together with all the diagnostic facts about the error that are deemed to be useful.

An alternative approach is to record the facts about the failure in a dedicated incidents table with a self-populating ticket_id primary key—and then to return the value of the ticket_id along with the failure status. A self-contained, downloadable implementation of this approach is implemented in the ysql-case-studies repo as the so-called hard-shell case study. Look at the implementations of the procedures api.insert_master_and_details() and support.insert_incident().

Notice, finally, that the procedure preferred() might too encounter an unexpected error that analysis failed to foresee. This explains the textually identical implementation of the inner block statement's when others handler in the outer block too. The code is uncomfortably repetitive. But the get stacked diagnostics statement can be used only within the handler where the facts about the caught exception are to be observed.

Now run the demo first by simulating the outcome that's expected to be common: the procedure preferred() simply succeeds.

call s.caller();

This is the outcome:

INFO:  preferred() succeeded.

Next, simulate the outcome where preferred() fails and where fallback() succeeds.

call s.caller(cause_preferred_failure=>true);

This is the outcome:

INFO:  Regrettable but not unexpected: preferred() failed.
INFO:  fallback() succeeded.

Finally, simulate the outcome where preferred() fails (as is expected that it can) and where then fallback() fails unexpectedly.

call s.caller(cause_preferred_failure=>true, cause_fallback_failure=>true);

This is the outcome:

INFO:  Regrettable but not unexpected: preferred() failed.
INFO:  Unexpected error: 22012
INFO:  
PL/pgSQL function s.fallback(boolean) line 6 at RAISE
SQL statement "CALL s.fallback(cause_fallback_failure)"
PL/pgSQL function s.caller(boolean,boolean) line 10 at CALL

do $body$
begin
  raise division_by_zero;
end;
$body$;

"raise exception" with the "using" syntax

A very strong case can be made that a user-written PL/pgSQL subprogram that your privilege regime allows to be invoked from client-side application code should never allow a SQL error to escape. Rather, every such subprogram should have a when others hander that records all the available diagnostic information server-side, where client-side application code cannot access it, and do no more than end without an error and return a suitably encoded response to express "unexpected error" and the ticket number for the incident report so that Support can analyze what went wrong. This philosophy is extended by the notion of the regrettable, but not unexpected, error—exemplified by "This nickname is taken. Please make a different choice." It is exactly this design paradigm that the hard-shell case study, referred to above, implements.

(Briefly, the rationale is that hackers can learn a lot that helps them to exploit vulnerabilities from the slew of names of schemas, tables, columns, and the like that raw error messages expose.)

However, there's another use case where more than just the bare fact that a regrettable but not unexpected error occurred needs to be signalled as an exception. Suppose that an application accepts a name that an end-user enters using an interactive client-side application. The name must obey certain rules and so, in accordance with the usual approach, the interactive application checks that a newly-entered name does indeed obey the rules so that a bad name can be immediately rejected. Then the user can try again with the minimum of delay. This implies that only properly conformant names will be sent to the database.

Of course, there's a possibility that the client-side check is buggy so that a non-conformant name is sent. This must be seen as an unexpected error. Facts about it must be recorded in an incidents table for offline analysis. But the database code doesn't need to be cluttered by code to communicate the outcome to the client application is it would for a regrettable, but not unexpected error.

The best way to deal with this scenario is at the deep level in the stack where a row with the name is to be inserted or updated. Then, if the conformance check for the name fails, a user-defined exception is raised with not just the dedicated error code for this but also further diagnostic information. The message attribute might be enough. But it might be better to split the diagnostic information between a generic message attribute and a specific detail attribute. Then the API-level subprogram, at the top of the call-stack, that the client application calls will catch this user-defined exception in a when others handler and look after recording the diagnostic information in the incidents table and telling the client just that an unexpected error occurred, together with the incident's ticket number as. (This can be done, for example, using a JSON document as has been discussed above.)

The following code illustrates this approach. First, create the procedure check_name() that finishes silently if the check succeeds and that raises an exception if it doesn't:

\c :db :u
drop schema if exists s cascade;
create schema s;

create procedure s.check_name(proposed_name in text)
  security definer
  language plpgsql
as $body$
declare
  ok   boolean not null := true;
  err  constant text not null := 'YB257';
  msg  constant text    not null :=
    'Bad name: "'||coalesce(proposed_name, '<NULL>')||'".'                    ||e'\n'||
    'A name must have length() between 5 and 30, '                            ||e'\n'||              
    'must contain only lower case ASCII(7) letters, digits, or underscores, ' ||e'\n'||
    'and must not start with a digit or an underscore. ';
  detail text not null := '';
begin
  if proposed_name is null then
    ok     := false;
    detail := 'Name is null.';
  elsif not length(proposed_name) between 5 and 30 then
    ok     := false;
    detail := 'Name is too short or too long.';
  else
    declare
      -- Remove all characters except lower-case Latin letters, digits, and underscores.
      n1 constant text not null := regexp_replace(proposed_name, '[^a-z0-9_]', '', 'g');

      -- Remove any digit or underscore in just the first postion.
      n2 constant text not null := regexp_replace(n1, '^[0-9_]', '');
    begin
      case n1 = proposed_name
        when false then
           ok     := false;
           detail := 'Name contains illegal character(s).'; 
        else
          case n2 = n1
            when false then
              ok     := false;
              detail := 'Name starts with digit or underscore.'; 
            else
              ok :=  true;
          end case;
      end case;
    end;
  end if;

  if not ok then
    raise exception using
      errcode := err,
      message := msg,
      detail  := detail;
  end if;
end;
$body$;

Now create a table function that emulates to top-of-stack subprogram—just for the purpose of demonstrating that the diagnostic information is available at the level:

create function s.check_name_outcome(proposed_name in text)
  returns table(z text)
  security definer
  language plpgsql
as $body$
begin
  call s.check_name(proposed_name);
  z := '"'||proposed_name||'" is OK.';                      return next;
exception
  when sqlstate 'YB257' then
    declare
      v_message_text         text not null := '';
      v_pg_exception_detail  text not null := '';
      v_pg_exception_context text not null := '';
    begin
      get stacked diagnostics
        v_message_text         := message_text,
        v_pg_exception_detail  := pg_exception_detail,
        v_pg_exception_context := pg_exception_context;

      z := v_message_text;                                  return next;
      z := '';                                              return next;
      z := v_pg_exception_detail;                           return next;
      z := '';                                              return next;
      z := v_pg_exception_context;                          return next;
    end;
end;
$body$;

Now test the scheme, first with a conformant name:

select s.check_name_outcome('catch_22');

This is the result:

 "catch_22" is OK.

Now test the scheme with a set of names, each of which violates one of the conformance rules so that all rules are tested. Use these names:

null
'abcd'
'catch$22'
'42mouse'
'_elephant'

Here's the output from using the first of these non-conformant names:

 Bad name: "<NULL>".                                                    +
 A name must have length() between 5 and 30,                            +
 must contain only lower case ASCII(7) letters, digits, or underscores, +
 and must not start with a digit or an underscore. 
 
 Name is null.
 
 PL/pgSQL function s.check_name(text) line 43 at RAISE                  +
 SQL statement "CALL s.check_name(proposed_name)"                       +
 PL/pgSQL function s.check_name_outcome(text) line 3 at CALL

All of the remaining negative tests show the same second and subsequent message lines, and the same context text as the first negative test—and so these lines are elided now. These are the remaining results:

 Bad name: "abcd".                                                      +
 ... 
 
 Name is too short or too long.

and:

 Bad name: "catch$22".                                                  +
 ...
 
 Name contains illegal character(s).

and:

 Bad name: "42mouse".                                                   +
 ... 
 
 Name starts with digit or underscore.

and:

 Bad name: "_elephant".                                                 +
 ... 
 
 Name starts with digit or underscore.

A similar scenario could arise if you're writing reusable subprograms for general use. (You might intend to package these up as an extension.) You can't control the use of such code—but you might, at least, expect that it will be called typically from application PL/pgSQL. Here, too, you have to deal with a regrettable but not unexpected error by raising a user-defined exception. You can see an example of this approach in the subsection Custom domain types for specializing the native interval functionality within the overall section Date and time data types and functionality. These domain types have constraints which are implemented in user-defined PL/pgSQL code. There, contrary to the general advice given above, the reserved error code 23514 (mapped to check_violation) is re-used.