Postgresql вывод сообщения об ошибке

Use the RAISE statement to report messages and raise errors.

RAISE [ level ] 'format' [, expression [, ... ]] [ USING option = expression [, ... ] ];
RAISE [ level ] condition_name [ USING option = expression [, ... ] ];
RAISE [ level ] SQLSTATE 'sqlstate' [ USING option = expression [, ... ] ];
RAISE [ level ] USING option = expression [, ... ];
RAISE ;

The level option specifies the error severity. Allowed levels are DEBUG, LOG, INFO, NOTICE, WARNING, and EXCEPTION, with EXCEPTION being the default. EXCEPTION raises an error (which normally aborts the current transaction); the other levels only generate messages of different priority levels. Whether messages of a particular priority are reported to the client, written to the server log, or both is controlled by the log_min_messages and client_min_messages configuration variables. See Chapter 20 for more information.

After level if any, you can specify a format string (which must be a simple string literal, not an expression). The format string specifies the error message text to be reported. The format string can be followed by optional argument expressions to be inserted into the message. Inside the format string, % is replaced by the string representation of the next optional argument’s value. Write %% to emit a literal %. The number of arguments must match the number of % placeholders in the format string, or an error is raised during the compilation of the function.

In this example, the value of v_job_id will replace the % in the string:

RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;

You can attach additional information to the error report by writing USING followed by option = expression items. Each expression can be any string-valued expression. The allowed option key words are:

This example will abort the transaction with the given error message and hint:

RAISE EXCEPTION 'Nonexistent ID --> %', user_id
      USING HINT = 'Please check your user ID';

These two examples show equivalent ways of setting the SQLSTATE:

RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';

There is a second RAISE syntax in which the main argument is the condition name or SQLSTATE to be reported, for example:

RAISE division_by_zero;
RAISE SQLSTATE '22012';

In this syntax, USING can be used to supply a custom error message, detail, or hint. Another way to do the earlier example is

RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;

Still another variant is to write RAISE USING or RAISE level USING and put everything else into the USING list.

The last variant of RAISE has no parameters at all. This form can only be used inside a BEGIN block’s EXCEPTION clause; it causes the error currently being handled to be re-thrown.

If no condition name nor SQLSTATE is specified in a RAISE EXCEPTION command, the default is to use ERRCODE_RAISE_EXCEPTION (P0001). If no message text is specified, the default is to use the condition name or SQLSTATE as message text.

The ASSERT statement is a convenient shorthand for inserting debugging checks into PL/pgSQL functions.

ASSERT condition [ , message ];

The condition is a Boolean expression that is expected to always evaluate to true; if it does, the ASSERT statement does nothing further. If the result is false or null, then an ASSERT_FAILURE exception is raised. (If an error occurs while evaluating the condition, it is reported as a normal error.)

If the optional message is provided, it is an expression whose result (if not null) replaces the default error message text “assertion failed”, should the condition fail. The message expression is not evaluated in the normal case where the assertion succeeds.

Testing of assertions can be enabled or disabled via the configuration parameter plpgsql.check_asserts, which takes a Boolean value; the default is on. If this parameter is off then ASSERT statements do nothing.

Note that ASSERT is meant for detecting program bugs, not for reporting ordinary error conditions. Use the RAISE statement, described above, for that.

In this article, we will look into the Errors in that are inbuilt in PostgreSQL and the process of raising an error in PostgreSQL through RAISE statement and to use the ASSERT statement to insert debugging checks into PL/pgSQL blocks.

To raise an error message user can implement the RAISE statement as follows:

Syntax: RAISE level format;

Let’s explore into the raise statement a bit more. Following the RAISE statement is the level option that specifies the error severity. PostgreSQL provides the following levels:

• DEBUG
• LOG
• NOTICE
• INFO
• WARNING
• EXCEPTION

If users don’t specify the level, by default, the RAISE statement will use the EXCEPTION level that raises an error and stops the current transaction. We will discuss the RAISE EXCEPTION later in the next section.

The format is a string that specifies the message. The format uses percentage ( %) placeholders that will be substituted by the next arguments. The number of placeholders must match the number of arguments, otherwise, PostgreSQL will report the following error message:

[Err] ERROR:  too many parameters specified for RAISE

Example:

The following example illustrates the RAISE statement that reports different messages at the current time.

DO $$ 
BEGIN 
  RAISE INFO 'information message %', now() ;
  RAISE LOG 'log message %', now();
  RAISE DEBUG 'debug message %', now();
  RAISE WARNING 'warning message %', now();
  RAISE NOTICE 'notice message %', now();
END $$;

Output:

Note: Not all messages are reported back to the client, only INFO, WARNING, and NOTICE level messages are reported to the client. This is controlled by the client_min_messages and log_min_messages configuration parameters.

Raising Errors:

To raise errors, you use the EXCEPTION level after the RAISE statement. Note that the RAISE statement uses the EXCEPTION level by default. Besides raising an error, you can add more detailed information by using the following clause with the RAISE statement:

USING option = expression

The options can be any one of the below:

• MESSAGE: set error message text
• HINT: provide the hint message so that the root cause of the error is easier to be discovered.
• DETAIL:  give detailed information about the error.
• ERRCODE: identify the error code, which can be either by condition name or directly five-character SQLSTATE code.

Example 1:

DO $$ 
DECLARE
  email varchar(255) := 'raju@geeksforgeeks.org';
BEGIN 
  -- check email for duplicate
  -- ...
  -- report duplicate email
  RAISE EXCEPTION 'Duplicate email: %', email 
        USING HINT = 'Check the email again';
END $$;

Output:

Example 2:

The following examples illustrate how to raise an SQLSTATE and its corresponding condition:

DO $$ 
BEGIN 
    --...
    RAISE SQLSTATE '2210B';
END $$;
DO $$ 
BEGIN 
    --...
    RAISE invalid_regular_expression;
END $$;

Output:

Last Updated :
28 Aug, 2020

Like Article

Save Article

PL/pgSQL is one of the most popular procedural languages in PostgreSQL. It provides the capability of creating functions and procedures that help the user perform reusable complex computations. A typical procedure is created using different procedural constructs including block structures, variables, SQL commands, and error-handling. 

In PostgreSQL, RAISE is used to report errors and messages. In this article, we will be focusing on how to use RAISE to implement error-handling within stored procedures and functions.

RAISE is used to raise errors and report messages, PostgreSQL provides various parameters to report an error, warning, and information at a detailed level. Below is the basic syntax for the RAISE command.

Syntax

RAISE [level] [format]

Level indicates error severity. The level can be (in order from least to most severe) DEBUG, LOG, INFO, NOTICE, WARNING, or EXCEPTION. EXCEPTION is the default level and the only one that will halt the procedure. Each level generates an error message with detailed information based on priority levels.

Users can control where these error messages will be reported (i.e., on client screen, server logs or on both) by setting the postgresql.conf parameters “log_min_messages” and “client_min_messages.”

Format specifies the error message that the user wants to display. If the message needs some variable values to be printed with it, the “%” sign is used. The “%” sign acts as a placeholder and is replaced by the variable value given with the RAISE command. This can be used multiple times. 

Let’s demonstrate all of this with an example:

postgres=# CREATE OR REPLACE PROCEDURE example1 () AS $$

postgres$# DECLARE

postgres$# a INT:= 10;

postgres$# BEGIN

postgres$# RAISE NOTICE 'value of a : %', a; 

postgres$# END;

postgres$# $$

postgres-# LANGUAGE plpgsql;

CREATE PROCEDURE

postgres=# CALL example1 ();

NOTICE:  value of a : 10

CALL

In this example “RAISE NOTICE ‘value of a : %’, a;” is used to print “NOTICE:  value of a : 10” when the procedure is called. The severity level of NOTICE is used to print the message “value of a” and “%” is replaced by the value of variable “a,” which is 10 in this instance.

RAISE INFO/WARNING/NOTICE

These are generally reported back to the client based on “client_min_messages” and “log_min_messages” settings. INFO, WARNING, and NOTICE can be used as named states to display warning information to the user.

postgres=# CREATE OR REPLACE PROCEDURE example2 () AS $$

postgres$# DECLARE

postgres$# a INT:= 10;

postgres$# BEGIN

postgres$# RAISE NOTICE 'value of a : % at %: ', a, now(); 

postgres$# a := a + 10;

postgres$# RAISE WARNING 'value of a : % at %: ', a, now(); 

postgres$# a := a + 10;

postgres$# RAISE INFO 'value of a : % at %: ', a, now(); 

postgres$# END;

postgres$# $$

postgres-# LANGUAGE plpgsql;

CREATE PROCEDURE

postgres=# CALL example2 ();

NOTICE:  value of a : 10 at 2019-11-24 18:26:34.919079+05:30: 

WARNING:  value of a : 20 at 2019-11-24 18:26:34.919079+05:30: 

INFO:  value of a : 30 at 2019-11-24 18:26:34.919079+05:30: 

CALL

RAISE LOG/DEBUG

The LOG and DEBUG levels are generally not reported back to the client under the default “client_min_messages” and “log_min_messages” settings. 

postgres=# CREATE OR REPLACE PROCEDURE example3 () AS $$

postgres$# DECLARE

postgres$# a INT:= 10;

postgres$# BEGIN

postgres$# RAISE LOG 'value of a : % at %: ', a, now(); 

postgres$# a := a + 10;

postgres$# RAISE DEBUG 'value of a : % at %: ', a, now(); 

postgres$# END;

postgres$# $$

postgres-# LANGUAGE plpgsql;

CREATE PROCEDURE

postgres=# CALL example3 ();

CALL

As seen above the procedure is called and nothing is printed to the client. By default, LOG will output a message in the server log. 

[edb@localhost bin]$ tail logfile 

2019-11-24 18:31:15.611 IST [85200] LOG:  value of a : 10 at 2019-11-24 18:31:15.610122+05:30: 

2019-11-24 18:31:15.611 IST [85200] CONTEXT:  PL/pgSQL function example3() line 5 at RAISE

2019-11-24 18:31:15.611 IST [85200] STATEMENT:  CALL example3 ();

DEBUG will output to the client when “client_min_messages” is set to the DEBUG level.

postgres=# set client_min_messages = debug;

SET

postgres=# CALL example3 ();

LOG:  value of a : 10 at 2019-11-24 18:34:17.902974+05:30: 

DEBUG:  value of a : 20 at 2019-11-24 18:34:17.902974+05:30: 

CALL

RAISE EXCEPTION

When no level is specified, EXCEPTION is used by default. EXCEPTION will abort the current transaction.

postgres=# CREATE OR REPLACE PROCEDURE example4 () AS $$

postgres$# DECLARE

postgres$# a INT:= 10;

postgres$# BEGIN

postgres$# RAISE 'value of a : %', a; 

postgres$# a := a + 10;

postgres$# RAISE INFO 'value of a : %', a; 

postgres$# END;

postgres$# $$

postgres-# LANGUAGE plpgsql;

CREATE PROCEDURE

postgres=# CALL example4 ();

ERROR:  value of a : 10

CONTEXT:  PL/pgSQL function example4() line 5 at RAISE

As seen above, when the RAISE statement does not specify a level, the message is printed as an ERROR, and the transaction is aborted, so the next RAISE statement isn’t executed. The same output will be produced when “RAISE EXCEPTION ‘value of a : %’, a;” is used instead.

RAISE can be used with various options to make the error message more readable and informative by using below syntax.

USING option = expression

Options can be MESSAGE, DETAIL, HINT, ERRCODE, etc., and expression is a single value statement.

postgres=# CREATE OR REPLACE PROCEDURE example5 () AS $$

postgres$# DECLARE

postgres$# a INT:= 10;

postgres$# BEGIN

postgres$# RAISE EXCEPTION 'value of a : %', a USING HINT = 'EXCEPTION is raised here and transaction aborted';

postgres$# a := a + 10;

postgres$# RAISE INFO 'value of a : %', a; 

postgres$# END;

postgres$# $$

postgres-# LANGUAGE plpgsql;

CREATE PROCEDURE

postgres=# CALL example5 ();

ERROR:  value of a : 10

HINT:  EXCEPTION is raised here and transaction aborted

CONTEXT:  PL/pgSQL function example5() line 5 at RAISE

Along with the ERROR message this example also outputs a HINT property back to the client. In a similar way, MESSAGE, DETAIL, and ERRCODE can also be used.

Reference Links

https://www.postgresql.org/docs/current/plpgsql-errors-and-messages.html

http://www.postgresqltutorial.com/plpgsql-errors-messages/

​

Перейти к содержимому