Если ошибка 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.

To catch the error message and its code:

do $$       
begin

    create table yyy(a int);
    create table yyy(a int); -- this will cause an error

exception when others then 

    raise notice 'The transaction is in an uncommittable state. '
                 'Transaction was rolled back';

    raise notice '% %', SQLERRM, SQLSTATE;

end; $$ 
language 'plpgsql';

Haven’t found the line number yet

UPDATE April, 16, 2019

As suggested by Diego Scaravaggi, for Postgres 9.2 and up, use GET STACKED DIAGNOSTICS:

do language plpgsql $$
declare
    v_state   TEXT;
    v_msg     TEXT;
    v_detail  TEXT;
    v_hint    TEXT;
    v_context TEXT;
begin

    create table yyy(a int);
    create table yyy(a int); -- this will cause an error

exception when others then 

    get stacked diagnostics
        v_state   = returned_sqlstate,
        v_msg     = message_text,
        v_detail  = pg_exception_detail,
        v_hint    = pg_exception_hint,
        v_context = pg_exception_context;

    raise notice E'Got exception:
        state  : %
        message: %
        detail : %
        hint   : %
        context: %', v_state, v_msg, v_detail, v_hint, v_context;

    raise notice E'Got exception:
        SQLSTATE: % 
        SQLERRM: %', SQLSTATE, SQLERRM;     

    raise notice '%', message_text; -- invalid. message_text is contextual to GET STACKED DIAGNOSTICS only

end; $$;

Result:

NOTICE:  Got exception:
        state  : 42P07
        message: relation "yyy" already exists
        detail : 
        hint   : 
        context: SQL statement "create table yyy(a int)"
PL/pgSQL function inline_code_block line 11 at SQL statement
NOTICE:  Got exception:
        SQLSTATE: 42P07 
        SQLERRM: relation "yyy" already exists

ERROR:  column "message_text" does not exist
LINE 1: SELECT message_text
               ^
QUERY:  SELECT message_text
CONTEXT:  PL/pgSQL function inline_code_block line 33 at RAISE
SQL state: 42703

Aside from GET STACKED DIAGNOSTICS is SQL standard-compliant, its diagnostics variables (e.g., message_text) are contextual to GSD only. So if you have a field named message_text in your table, there’s no chance that GSD can interfere with your field’s value.

Still no line number though.

Summary: in this tutorial, you will learn how to catch PostgreSQL exceptions in PL/pgSQL.

Introduction to the PL/pgSQL Exception clause

When an error occurs in a block, PostgreSQL will abort the execution of the block and also the surrounding transaction.

To recover from the error, you can use the exception clause in the begin...end block.

The following illustrates the syntax of the exception clause:

<>
 declare
 begin
     statements;
 exception
     when condition [or condition…] then
        handle_exception;
    [when condition [or condition…] then
        handle_exception;]
    [when others then
        handle_other_exceptions;
    ]
 end;

How it works.

• First, when an error occurs between the begin and exception, PL/pgSQL stops the execution and passes the control to the exception list.
• Second, PL/pgSQL searches for the first condition that matches the occurring error.
• Third, if there is a match, the corresponding handle_exception statements will execute. PL/pgSQL passes the control to the statement after the end keyword.
• Finally, if no match found, the error propagates out and can be caught by the exception clause of the enclosing block. In case there is no enclosing block with the exception clause, PL/pgSQL will abort the processing.

The condition names can be no_data_found in case of a select statement return no rows or too_many_rows if the select statement returns more than one row. For a complete list of condition names on the PostgreSQL website.

It’s also possible to specify the error condition by SQLSTATE code. For example, P0002 for no_data_found and P0003 for too_many_rows.

Typically, you will catch a specific exception and handle it accordingly. To handle other exceptions rather than the one you specify on the list, you can use the when others then clause.

Handling exception examples

We’ll use the film table from the sample database for the demonstration.

1. Handling no_data_found exception example

The following example issues an error because the film with id 2000 does not exist.

do
 $$
 declare
     rec record;
     v_film_id int = 2000;
 begin
     -- select a film 
     select film_id, title 
     into strict rec
     from film
     where film_id = v_film_id;
 end;
 $$
 language plpgsql;

Output:

ERROR:  query returned no rows
 CONTEXT:  PL/pgSQL function inline_code_block line 6 at SQL statement
 SQL state: P0002

The following example uses the exception clause to catch the no_data_found exception and report a more meaningful message:

do
 $$
 declare
     rec record;
     v_film_id int = 2000;
 begin
     -- select a film 
     select film_id, title 
     into strict rec
     from film
     where film_id = v_film_id;
         -- catch exception
     exception 
        when no_data_found then 
           raise exception 'film % not found', v_film_id;
 end;
 $$
 language plpgsql;

Output:

ERROR:  film 2000 not found
 CONTEXT:  PL/pgSQL function inline_code_block line 14 at RAISE
 SQL state: P0001

2. Handling too_many_rows exception example

The following example illustrates how to handle the too_many_rows exception:

do
 $$
 declare
     rec record;
 begin
     -- select film 
     select film_id, title 
     into strict rec
     from film
     where title LIKE 'A%';
 exception     when too_many_rows then       raise exception 'Search query returns too many rows';
 end;
 $$
 language plpgsql;

Output:

ERROR:  Search query returns too many rows
 CONTEXT:  PL/pgSQL function inline_code_block line 15 at RAISE
 SQL state: P0001

In this example, the too_many_rows exception occurs because the select into statement returns more than one row while it is supposed to return one row.

3. Handling multiple exceptions

The following example illustrates how to catch multiple exceptions:

do
 $$
 declare
     rec record;
     v_length int = 90;
 begin
     -- select a film 
     select film_id, title 
     into strict rec
     from film
     where length = v_length;
     -- catch exception exception     when sqlstate 'P0002' then        raise exception 'film with length % not found', v_length;    when sqlstate 'P0003' then        raise exception 'The with length % is not unique', v_length;
 end;
 $$
 language plpgsql;

Output:

ERROR:  The with length 90 is not unique
 CONTEXT:  PL/pgSQL function inline_code_block line 17 at RAISE
 SQL state: P0001

4. Handling exceptions as SQLSTATE codes

The following example is the same as the one above except that it uses the SQLSTATE codes instead of the condition names:

do
 $$
 declare
     rec record;
     v_length int = 30;
 begin
     -- select a film 
     select film_id, title 
     into strict rec
     from film
     where length = v_length;
     -- catch exception exception     when sqlstate 'P0002' then        raise exception 'film with length % not found', v_length;    when sqlstate 'P0003' then        raise exception 'The with length % is not unique', v_length;
 end;
 $$
 language plpgsql;

Output:

ERROR:  film with length 30 not found
 CONTEXT:  PL/pgSQL function inline_code_block line 15 at RAISE
 SQL state: P0001

Summary

• Use the exception clause in the begin...end block to catch and handle exceptions.

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