The PL/pgSQL "raise" statement
For production, use the latest stable version (v2024.1).
Syntax
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:
"Raise" using item | Diagnostics item name | Description |
---|---|---|
errcode | returned_sqlstate | The code of the error that caused the exception. |
message | message_text | The text of the exception's primary message. |
detail | pg_exception_detail | The text of the exception's detail message. |
hint | pg_exception_hint | The text of the exception's hint message. |
schema | schema_name | The name of the schema related to exception. |
table | table_name | The name of the table related to exception. |
column | column_name | The name of the column related to exception. |
datatype | pg_datatype_name | The name of the data type related to exception. |
constraint | constraint_name | The name of the constraint related to exception. |
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.)
The log level is lower than the notice level. The report text from raise log will, therefore, not be rendered unless client_min_messages is set to log (or to any one of the levels from (bare) debug, debug1, and so on through debug5). But if you set client_min_messages to log, then you'll see many message texts that the internal implementation of top-level SQL statements generates. Try this example:
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$;
As expected, it completes silently. Now set client_min_messages to log and repeat the do statement. Now you see this on stderr:
LOG: "drop table if exists" invoked
NOTICE: table "t" does not exist, skipping
LOG: "create table" invoked
LOG: "drop table" invoked
Notice that, in addition to the three intended log level message texts, a report text at notice level, from the internal implementation, is rendered thus: table "t" does not exist, skipping. This is because the notice level has higher priority than the log level.
The intended benefit of the log level is that you can permanently include tracing output in your code. Such tracing text can, when appropriate, render locally available values. Then you can turn the trace output on or off at run-time on a per-session basis. However, the trace output might become be so cluttered that it will be effectively unreadable.
You get significantly cluttered outcome when you specify the debug level in the raise statement. Try this:
set client_min_messages = debug;
and repeat the do same statement. Now you see this on stderr:
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
You can implement a simple dynamically controllable scheme to render clean tracing like this:
\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$;
There's more code than you might, at first, have expected because this:
trace_on = lower(current_setting('trace.mode')) = 'true';
will cause an error if the user-defined run-time parameter trace.mode doesn't exist. No such parameters exist at session start. One is implicitly created (with just session-duration) when a value is first assigned to it. By definition, here, when the value is true this means that tracing is requested in the present session. It's natural to define that non-existence of trace.mode, along with any value other than true that it might have, means that tracing is not requested in the present session. It's good practice, when you handle an error in order simply to interpret this as a benign expected outcome and to suppress it, to check that your reasoning is sound. That's why the handler goes on to check not just the error code but also that the primary message text is exactly what is expected.
Procedure 'utils.trace()' nicely illustrates the inevitability of expected errors
The term "expected error" tautologically captures the notion that some errors are simply bound to occur in some scenarios—and that such an occurrence can be benign. The Procedure utils.trace() perfectly illustrates the case where sometimes ordinary program actions must be implemented in an exception handler. A block statement with an exception section with such a handler will almost always be an inner block within the block statement that implements the main program logic.Here's how to use the utils.trace() procedure:
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$;
Test it when the user-defined run-time parameter trace.mode doesn't yet exist:
\c :db :u
set client_min_messages = warning;
call s.p1();
This finishes silently because tracing isn't yet turned on. Now turn it on and test again:
set trace.mode = true;
call s.p1();
This output is rendered on stderr:
INFO: "drop table if exists" invoked
INFO: "create table" invoked
INFO: "drop table" invoked
Now turn tracing off and test again:
set trace.mode = 42;
call s.p1();
This finishes silently, as intended. (All user-defined run-time parameter settings are observed as text values—and so 42 has the same effect as false.)
It's easy, now, to create a second two-argument overload of util.trace() that adds a kind argument to give more granular control over what is traced and what isn't.
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$;
The utils.trace() procedure uses native array functionality to transform a text value like 'a, b, c, d' into (in this example) the text[] array array['a', 'b', 'c', 'd']. And it uses the contains operator, <@, to test if a value of interest is among the array's elements.
Here's how to use the two-argument overload of the utils.trace() procedure:
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$;
Test it when the user-defined run-time parameter trace.kinds doesn't yet exist:
\c :db :u
set client_min_messages = warning;
call s.p2();
This finishes silently because tracing isn't yet turned on. Now request just the blue kind of trace output and test again:
set trace.kinds = 'blue';
call s.p2();
This output is rendered on stderr:
INFO: Blue message.
Now request just the green kind of trace output (testing the intended case-insensitivity) and test again:
set trace.kinds = 'Green';
call s.p2();
This output is rendered on stderr:
INFO: Green message.
Now request the red, the blue, and the yellow kinds of trace output and test again:
set trace.kinds = 'red, BLUE, Yellow';
call s.p2();
This output is rendered on stderr:
INFO: Red message.
INFO: Blue message.
INFO: Yellow message.
Finally, turn off all kinds of trace output and test again:
set trace.kinds = '';
call s.p2();
This finishes silently, as intended.
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".
The term "hybrid" is used here to denote the syntax that has both the shortcut clause and the using clause.
You might decide to adopt a simple rule never to use the shortcut clause. This is a viable approach because the using clause expresses a superset of the semantics that the shortcut clause can express.
Alternatively, when you want to specify only using options that the shortcut clause can express, you might prefer to use the shortcut clause for such a case in order to make your code briefer.
"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
This section has explained how raising a user-defined exception, identified by an error code that PostgreSQL has not reserved, can be useful for implementing long jump-like functionality. The approach relies on the fact that an error code is not checked during syntax analysis against those that are listed in the table of Error Codes and Condition Names in the PostgreSQL documentation. (The only syntax check is that the code is a mixture of five digits or upper-case Latin letters.) In contrast, when you raise an exception using its name, a syntax error occurs if the name is not found among the condition names that the table lists. In other words, the raise <exception name> syntax lets you raise only exceptions that the PostgreSQL system itself can raise (and YSQL directly reuses the PostgreSQL implementation for this).
Nothing stops you doing this:
do $body$
begin
raise division_by_zero;
end;
$body$;
But doing so is at odds with the fact that the raise syntax tautologically raises a user-defined exception. (It's user-defined exactly because the exception is raised as a consequence of logic that user-written code implements.) In other words, if you use, say, raise division_by_zero, then you're lying because your code, and not the PostgreSQL implementation, detected that you should do this—and therefore the error is not, from the strictly formal viewpoint, the one that division_by_zero denotes.
"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.