![]() ![]() SQL state: P0001 Code language: Shell Session ( shell ) 4) Handling exceptions as SQLSTATE codes Output: ERROR: The with length 90 is not uniqueĬONTEXT: PL/pgSQL function inline_code_block line 17 at RAISE When sqlstate 'P0003' then raise exception 'The with length % is not unique', v_length catch exception exception when sqlstate 'P0002' then raise exception 'film with length % not found', v_length The following example illustrates how to catch multiple exceptions: do 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. SQL state: P0001 Code language: Shell Session ( shell ) Output: ERROR: Search query returns too many rowsĬONTEXT: PL/pgSQL function inline_code_block line 15 at RAISE The following example illustrates how to handle the too_many_rows exception: doīegin - select film select film_id, titleĮxception when too_many_rows then raise exception 'Search query returns too many rows' SQL state: P0001 Code language: Shell Session ( shell ) 2) Handling too_many_rows exception example $$ language plpgsql Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql )ĬONTEXT: PL/pgSQL function inline_code_block line 14 at RAISE catch exception exception when no_data_found then raise exception 'film % not found', v_film_id The following example uses the exception clause to catch the no_data_found exception and report a more meaningful message: do SQL state: P0002 Code language: Shell Session ( shell ) doīegin - select a film select film_id, titleĬode language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql )ĬONTEXT: PL/pgSQL function inline_code_block line 6 at SQL statement The following example issues an error because the film with id 2000 does not exist. 1) Handling no_data_found exception example We’ll use the film table from the sample database for the demonstration. To handle other exceptions rather than the one you specify on the list, you can use the when others then clause. Typically, you will catch a specific exception and handle it accordingly. For example, P0002 for no_data_found and P0003 for too_many_rows. It’s also possible to specify the error condition by SQLSTATE code. For a complete list of condition names on the PostgreSQL website. 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. In case there is no enclosing block with the exception clause, PL/pgSQL will abort the processing. Finally, if no match found, the error propagates out and can be caught by the exception clause of the enclosing block.PL/pgSQL passes the control to the statement after the end keyword. Third, if there is a match, the corresponding handle_exception statements will execute.Second, PL/pgSQL searches for the first condition that matches the occurring error.First, when an error occurs between the begin and exception, PL/pgSQL stops the execution and passes the control to the exception list.The following illustrates the syntax of the exception clause: > declare beginĮxception when condition thenĮnd Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) To recover from the error, you can use the exception clause in the begin.end block. When an error occurs in a block, PostgreSQL will abort the execution of the block and also the surrounding transaction. Introduction to the PL/pgSQL Exception clause Summary: in this tutorial, you will learn how to catch PostgreSQL exceptions in PL/pgSQL. ![]()
0 Comments
Leave a Reply. |