Plpgsql raise error

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.

Introduction to PostgreSQL RAISE EXCEPTION

PostgreSQL raises an exception is used to raise the statement for reporting the warnings, errors and other type of reported message within a function or stored procedure. We are raising the exception in function and stored procedures in PostgreSQL; there are different level available of raise exception, i.e. info, notice, warning, debug, log and notice. We can basically use the raise exception statement to raise errors and report the messages; by default, the exception level is used in the raise exception. We can also add the parameter and variable in the raise exception statement; also, we can print our variable’s value.

Syntax:

Given below is the syntax:

RAISE [LEVEL] (Level which we have used with raise exception statement.) [FORMAT]

OR

RAISE [ LEVEL] USING option (Raise statement using option) = expression

OR

RAISE;

Parameters Description:

• RAISE: This is defined as an exception statement that was used to raise the exception in PostgreSQL. We have basically pass two parameters with raise exception, i.e. level and format. There is various parameter available to raise an error.
• LEVEL: Level in raise exception is defined as defining the error severity. We can use level in raise exception, i.e. log, notice, warning, info, debug and exception. Every level generates detailed information about the error or warning message based on the priority of levels. By default, the exception level is used with raise statement in PostgreSQL, log_min_messages and client_min_messages parameter will be used to control the database server logging.
• FORMAT: This is defined as an error message which we want to display. If our message needs some variable value, then we need to use the % sign. This sign acts as a placeholder that replaces the variable value with a given command.
• expression: We can use multiple expression with raise expression statement in it. The expression is an optional parameter that was used with the raise expression statement.
• option: We can use options with raise exception statement in PostgreSQL.

How RAISE EXCEPTION work in PostgreSQL?

We can raise the exception by violating the data integrity constraints in PostgreSQL. Raise exception is basically used to raise the error and report the messages.

Given below shows the raise statement-level options, which was specifying the error severity in PostgreSQL:

• Notice
• Log
• Debug
• Warning
• Info
• Exception

If we need to raise an error, we need to use the exception level after using the raise statement in it.

We can also add more detailed information about the error by using the following clause with the raise exception statement in it.

USING option = expression

We can also provide an error message that is used to find the root cause of the error easier, and it is possible to discover the error. The exception gives an error in the error code; we will identify the error using the error code; it will define either a SQL State code condition. When the raise statement does not specify the level, it will specify the printed message as an error. After printing, the error message currently running transaction is aborted, and the next raise statement is not executed.

It is used in various parameters or options to produce an error message that is more informative and readable. When we are not specifying any level, then by default, the exception level is used in the raise statement. The exception level is aborted the current transaction with a raise statement in it. The exception level is very useful and important to raise the statement to abort the current transaction in it.

Examples of PostgreSQL RAISE EXCEPTION

Given below are the examples mentioned:

Example #1

Raise an exception statement, which reports different messages.

• The below example shows the raise exception statement, which reports different messages.
• The below example shows the raise statement, which was used to report the different messages at the current time stamp.

Code:

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

Output:

In the above example, only info, warning, notice and exception will display the information. But debug and log will not display the output information.

Example #2

Raise error using raise exception.

• The below example shows that raise the error using raise exception in PostgreSQL.
• We have added more detailed information by adding an exception.

Code:

DO $$
DECLARE
personal_email varchar(100) := 'raise@exceptions.com';
BEGIN
-- First check the user email id is correct as well as duplicate or not.
-- If user email id is duplicate then report mail as duplicate.
RAISE EXCEPTION 'Enter email is duplicate: %', personal_email
USING HINT = 'Check email and enter correct email ID of user';
END $$;

Output:

Example #3

Raise exception by creating function.

• The below example shows that raise exception in PostgreSQL by creating the function.

Code:

create or replace function test_exp() returns void as
$$
begin
raise exception using message = 'S 167', detail = 'D 167', hint = 'H 167', errcode = 'P3333';
end;
$$ language plpgsql
;

Output:

Advantages of PostgreSQL RAISE EXCEPTION

Below are the advantages:

• The main advantage of raise exception is to raise the statement for reporting the warnings.
• We have used raise exception in various parameters.
• Raise exception to have multiple levels to raise the error and warning.
• Raise statement is used the level of exception to show warnings and error.

Conclusion

RAISE EXCEPTION in PostgreSQL is basically used to raise the warning and error message. It is very useful and important. There are six levels of raise exception is available in PostgreSQL, i.e. notice, log, debug, warning info and exception. It is used in various parameters.

This is a guide to PostgreSQL RAISE EXCEPTION. Here we discuss how to RAISE EXCEPTION work in PostgreSQL, its advantages and examples. You may also have a look at the following articles to learn more –

1. PostgreSQL ARRAY_AGG()
2. PostgreSQL IF Statement
3. PostgreSQL Auto Increment
4. PostgreSQL replace

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:

Postgresql raise exception примеры

Use the RAISE statement to report messages and raise errors.

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:

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:

Sets the error message text. This option can’t be used in the form of RAISE that includes a format string before USING .

Supplies an error detail message.

Supplies a hint message.

Specifies the error code (SQLSTATE) to report, either by condition name, as shown in Appendix A, or directly as a five-character SQLSTATE code.

COLUMN
CONSTRAINT
DATATYPE
TABLE
SCHEMA

Supplies the name of a related object.

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

These two examples show equivalent ways of setting the SQLSTATE:

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

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

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.

Before PostgreSQL 9.1, RAISE without parameters was interpreted as re-throwing the error from the block containing the active exception handler. Thus an EXCEPTION clause nested within that handler could not catch it, even if the RAISE was within the nested EXCEPTION clause’s block. This was deemed surprising as well as being incompatible with Oracle’s PL/SQL.

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.

When specifying an error code by SQLSTATE code, you are not limited to the predefined error codes, but can select any error code consisting of five digits and/or upper-case ASCII letters, other than 00000 . It is recommended that you avoid throwing error codes that end in three zeroes, because these are category codes and can only be trapped by trapping the whole category.

43.9.2. Checking Assertions

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

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.

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Copyright © 1996-2023 The PostgreSQL Global Development Group

Источник

Postgresql raise exception примеры

Команда RAISE предназначена для вывода сообщений и вызова ошибок.

уровень задаёт уровень важности ошибки. Возможные значения: DEBUG , LOG , INFO , NOTICE , WARNING и EXCEPTION . По умолчанию используется EXCEPTION . EXCEPTION вызывает ошибку (что обычно прерывает текущую транзакцию), остальные значения уровня только генерируют сообщения с различными уровнями приоритета. Будут ли сообщения конкретного приоритета переданы клиенту или записаны в журнал сервера, или и то, и другое, зависит от конфигурационных переменных log_min_messages и client_min_messages. За дополнительными сведениями обратитесь к Главе 18.

После указания уровня , если оно есть, можно задать строку формата (это должна быть простая строковая константа, не выражение). Строка формата определяет вид текста об ошибке, который будет выдан. За строкой формата могут следовать необязательные выражения аргументов, которые будут вставлены в сообщение. Внутри строки формата знак % заменяется строковым представлением значения очередного аргумента. Чтобы выдать символ % буквально, продублируйте его (как %% ). Число аргументов должно совпадать с числом местозаполнителей % в строке формата, иначе при компиляции функции возникнет ошибка.

В следующем примере символ % будет заменён на значение v_job_id :

При помощи USING и последующих элементов параметр = выражение можно добавить дополнительную информацию к отчёту об ошибке. Все выражения представляют собой строковые выражения. Возможные ключевые слова для параметра следующие:

Устанавливает текст сообщения об ошибке. Этот параметр не может использоваться, если в команде RAISE присутствует формат перед USING . DETAIL

Предоставляет детальное сообщение об ошибке. HINT

Предоставляет подсказку по вызванной ошибке. ERRCODE

Устанавливает код ошибки ( SQLSTATE ). Код ошибки задаётся либо по имени, как показано в Приложении A, или напрямую, пятисимвольный код SQLSTATE . COLUMN
CONSTRAINT
DATATYPE
TABLE
SCHEMA

Предоставляет имя соответствующего объекта, связанного с ошибкой.

Этот пример прерывает транзакцию и устанавливает сообщение об ошибке с подсказкой:

Следующие два примера демонстрируют эквивалентные способы задания SQLSTATE :

У команды RAISE есть и другой синтаксис, в котором в качестве главного аргумента используется имя или код SQLSTATE ошибки. Например:

Предложение USING в этом синтаксисе можно использовать для того, чтобы переопределить стандартное сообщение об ошибке, детальное сообщение, подсказку. Ещё один вариант предыдущего примера:

Ещё один вариант — использовать RAISE USING или RAISE уровень USING , а всё остальное записать в списке USING .

И заключительный вариант, в котором RAISE не имеет параметров вообще. Эта форма может использоваться только в секции EXCEPTION блока и предназначена для того, чтобы повторно вызвать ошибку, которая сейчас перехвачена и обрабатывается.

Примечание

До версии PostgreSQL 9.1 команда RAISE без параметров всегда вызывала ошибку с выходом из блока, содержащего активную секцию EXCEPTION . Эту ошибку нельзя было перехватить, даже если RAISE в секции EXCEPTION поместить во вложенный блок со своей секцией EXCEPTION . Это было сочтено удивительным и не совместимым с Oracle PL/SQL.

Если в команде RAISE EXCEPTION не задано ни имя условия, ни код SQLSTATE, по умолчанию выдаётся исключение ERRCODE_RAISE_EXCEPTION ( P0001 ). Если не задан текст сообщения, по умолчанию в качестве этого текста передаётся имя условия или код SQLSTATE.

Примечание

При задании SQLSTATE кода необязательно использовать только список предопределённых кодов ошибок. В качестве кода ошибки может быть любое пятисимвольное значение, состоящее из цифр и/или ASCII символов в верхнем регистре, кроме 00000 . Не рекомендуется использовать коды ошибок, которые заканчиваются на 000 , потому что так обозначаются коды категорий. И чтобы их перехватить, нужно перехватывать целую категорию.

41.9.2. Проверка утверждений

Оператор ASSERT представляет удобное средство вставлять отладочные проверки в функции PL/pgSQL .

Здесь условие — это логическое выражение, которое, как ожидается, должно быть всегда истинным; если это так, оператор ASSERT больше ничего не делает. Если же оно возвращает ложь или NULL, этот оператор выдаёт исключение ASSERT_FAILURE . (Если ошибка происходит при вычислении условия , она выдаётся как обычная ошибка.)

Если в нём задаётся необязательное сообщение , результат этого выражения (если он не NULL) заменяет сообщение об ошибке по умолчанию « assertion failed » (нарушение истинности), в случае, если условие не выполняется. В обычном случае, когда условие утверждения выполняется, выражение сообщения не вычисляется.

Проверку утверждений можно включить или отключить с помощью конфигурационного параметра plpgsql.check_asserts , принимающего логическое значение; по умолчанию она включена ( on ). Если этот параметр отключён ( off ), операторы ASSERT ничего не делают.

Учтите, что оператор ASSERT предназначен для выявления программных дефектов, а не для вывода обычных ошибок (для этого используется оператор RAISE , описанный выше).

Источник

Postgresql raise exception примеры

Команда RAISE предназначена для вывода сообщений и вызова ошибок.

уровень задаёт уровень важности ошибки. Возможные значения: DEBUG , LOG , INFO , NOTICE , WARNING и EXCEPTION . По умолчанию используется EXCEPTION . EXCEPTION вызывает ошибку (что обычно прерывает текущую транзакцию), остальные значения уровня только генерируют сообщения с различными уровнями приоритета. Будут ли сообщения конкретного приоритета переданы клиенту или записаны в журнал сервера, или и то, и другое, зависит от конфигурационных переменных log_min_messages и client_min_messages. За дополнительными сведениями обратитесь к Главе 20.

После указания уровня , если оно есть, можно задать строку формата (это должна быть простая строковая константа, не выражение). Строка формата определяет вид текста об ошибке, который будет выдан. За строкой формата могут следовать необязательные выражения аргументов, которые будут вставлены в сообщение. Внутри строки формата знак % заменяется строковым представлением значения очередного аргумента. Чтобы выдать символ % буквально, продублируйте его (как %% ). Число аргументов должно совпадать с числом местозаполнителей % в строке формата, иначе при компиляции функции возникнет ошибка.

В следующем примере символ % будет заменён на значение v_job_id :

При помощи USING и последующих элементов параметр = выражение можно добавить дополнительную информацию к отчёту об ошибке. Все выражения представляют собой строковые выражения. Возможные ключевые слова для параметра следующие:

Устанавливает текст сообщения об ошибке. Этот параметр не может использоваться, если в команде RAISE присутствует формат перед USING . DETAIL

Предоставляет детальное сообщение об ошибке. HINT

Предоставляет подсказку по вызванной ошибке. ERRCODE

Устанавливает код ошибки ( SQLSTATE ). Код ошибки задаётся либо по имени, как показано в Приложении A, или напрямую, пятисимвольный код SQLSTATE . COLUMN
CONSTRAINT
DATATYPE
TABLE
SCHEMA

Предоставляет имя соответствующего объекта, связанного с ошибкой.

Этот пример прерывает транзакцию и устанавливает сообщение об ошибке с подсказкой:

Следующие два примера демонстрируют эквивалентные способы задания SQLSTATE :

У команды RAISE есть и другой синтаксис, в котором в качестве главного аргумента используется имя или код SQLSTATE ошибки. Например:

Предложение USING в этом синтаксисе можно использовать для того, чтобы переопределить стандартное сообщение об ошибке, детальное сообщение, подсказку. Ещё один вариант предыдущего примера:

Ещё один вариант — использовать RAISE USING или RAISE уровень USING , а всё остальное записать в списке USING .

И заключительный вариант, в котором RAISE не имеет параметров вообще. Эта форма может использоваться только в секции EXCEPTION блока и предназначена для того, чтобы повторно вызвать ошибку, которая сейчас перехвачена и обрабатывается.

Примечание

До версии PostgreSQL 9.1 команда RAISE без параметров всегда вызывала ошибку с выходом из блока, содержащего активную секцию EXCEPTION . Эту ошибку нельзя было перехватить, даже если RAISE в секции EXCEPTION поместить во вложенный блок со своей секцией EXCEPTION . Это было сочтено удивительным и не совместимым с Oracle PL/SQL.

Если в команде RAISE EXCEPTION не задано ни имя условия, ни код SQLSTATE, по умолчанию выдаётся исключение ERRCODE_RAISE_EXCEPTION ( P0001 ). Если не задан текст сообщения, по умолчанию в качестве этого текста передаётся имя условия или код SQLSTATE.

Примечание

При задании SQLSTATE кода необязательно использовать только список предопределённых кодов ошибок. В качестве кода ошибки может быть любое пятисимвольное значение, состоящее из цифр и/или ASCII символов в верхнем регистре, кроме 00000 . Не рекомендуется использовать коды ошибок, которые заканчиваются на 000 , потому что так обозначаются коды категорий. И чтобы их перехватить, нужно перехватывать целую категорию.

43.9.2. Проверка утверждений

Оператор ASSERT представляет удобное средство вставлять отладочные проверки в функции PL/pgSQL .

Здесь условие — это логическое выражение, которое, как ожидается, должно быть всегда истинным; если это так, оператор ASSERT больше ничего не делает. Если же оно возвращает ложь или NULL, этот оператор выдаёт исключение ASSERT_FAILURE . (Если ошибка происходит при вычислении условия , она выдаётся как обычная ошибка.)

Если в нём задаётся необязательное сообщение , результат этого выражения (если он не NULL) заменяет сообщение об ошибке по умолчанию « assertion failed » (нарушение истинности), в случае, если условие не выполняется. В обычном случае, когда условие утверждения выполняется, выражение сообщения не вычисляется.

Проверку утверждений можно включить или отключить с помощью конфигурационного параметра plpgsql.check_asserts , принимающего логическое значение; по умолчанию она включена ( on ). Если этот параметр отключён ( off ), операторы ASSERT ничего не делают.

Учтите, что оператор ASSERT предназначен для выявления программных дефектов, а не для вывода обычных ошибок (для этого используется оператор RAISE , описанный выше).

Источник

RAISE

RAISE Notices in plpgsql are generally used for two reasons:

• As a simple debugging tool to output state variables in a function call.
• As a WARNING to a user to inform them of important things such as this function is deprecated and should not be used or they are using something in an incorrect way.

A simple example of notices and recursion is shown below. Admittedly I couldn’t come up with a more pointless example to demonstrate recursion:

CREATE OR REPLACE FUNCTION fnsomefunnote(param_numcount integer)
  RETURNS integer AS
$$
DECLARE
BEGIN
    IF param_numcount > 0 THEN
        RAISE NOTICE 'Yo there I''m number %, next: %', param_numcount, param_numcount -1;
        RETURN fnsomefunnote(param_numcount - 1);
    ELSE
        RETURN param_numcount;
    END IF;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;

SELECT fnsomefunnote(4);

Returns 0 and also notices - if you are looking at this in PgAdminIII 
you shall see this on the messages tab

NOTICE:  Yo there I'm number 4, next: 3
NOTICE:  Yo there I'm number 3, next: 2
CONTEXT:  PL/pgSQL function "fnsomefunnote" line 5 at RETURN
NOTICE:  Yo there I'm number 2, next: 1
CONTEXT:  PL/pgSQL function "fnsomefunnote" line 5 at RETURN
PL/pgSQL function "somefunnote" line 5 at RETURN
NOTICE:  Yo there I'm number 1, next: 0
CONTEXT:  PL/pgSQL function "fnsomefunnote" line 5 at RETURN
PL/pgSQL function "fnsomefunnote" line 5 at RETURN

RAISE also has other variants namely DEBUG(1-5), LOG, INFO, EXCEPTION

DEBUG, LOG, and INFO are just different levels of NOTICE and only vary depending on which logs they get written to and if they get written to client. By default NOTICE is always
written to the client. These are controlled by the postgresql.conf client_min_messages and log_min_messages.

RAISE EXCEPTION is slightly different. It both displays an error message and halts further excecution of the stored function.

Below is a slightly different variant of the above: Also note here we follow the general practice of having a single point of return.
Having a single point of return tends to make your code easier to read and debug.

CREATE OR REPLACE FUNCTION fnsomemorefunnote(param_numcount integer)
  RETURNS integer AS
$$
DECLARE result integer;
BEGIN
    IF param_numcount < 0 THEN
        RAISE EXCEPTION 'Negative numbers are not allowed';
    ELSIF param_numcount > 0 THEN
        RAISE NOTICE 'Yo there I''m number %, next: %', param_numcount, param_numcount -1;
        result := fnsomemorefunnote(param_numcount - 1);
    ELSE
        RAISE INFO 'Alas we are at the end of our journey';
        result := param_numcount;
    END IF;
    RETURN result;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;


--------
SELECT fnsomemorefunnote(n)
FROM generate_series(-1,2) As n;

ERROR:  Negative numbers are not allowed

********** Error **********

ERROR: Negative numbers are not allowed
SQL state: P0001
---------

--no result is returned--


SELECT fnsomemorefunnote(n)
FROM generate_series(0,2) As n;
--result--
0
0
0


--messages--
INFO:  Alas we are at the end of our journey
NOTICE:  Yo there I'm number 1, next: 0
INFO:  Alas we are at the end of our journey
CONTEXT:  PL/pgSQL function "fnsomemorefunnote" line 7 at assignment
NOTICE:  Yo there I'm number 2, next: 1
NOTICE:  Yo there I'm number 1, next: 0
CONTEXT:  PL/pgSQL function "fnsomemorefunnote" line 7 at assignment
INFO:  Alas we are at the end of our journey
CONTEXT:  PL/pgSQL function "fnsomemorefunnote" line 7 at assignment
PL/pgSQL function "fnsomemorefunnote" line 7 at assignment

Total query runtime: 16 ms.
3 rows retrieved.

Storing values in Temp variables, FOUND and more recursion

Sometimes you have the need to store intermediate results in a temp variable from a query to use for later processing. Here
is a simple example of that.

FOUND is a state variable that contains either true or false if the last result returning query returned records or not.

Below is an example that demonstrates, FOUND, storing sql values in temp variables, and more recursion.

This example is the plpgsql equivalent to our SQL Server Transact-SQL article called Using SQL Server 2000’s User Defined Function to solve the Tree Problem

Also check out Using PostGreSQL User-Defined Functions to solve the Tree Problem

CREATE TABLE employees(employeeid varchar(50) PRIMARY KEY, managerid varchar(50));
--8.2+ syntax for 8.1 and below need to do individual insert statements
INSERT INTO employees(employeeid, managerid)
VALUES ('Diana', Null), ('Peter', 'Diana'), 
    ('Nancy', 'Peter'), ('John', 'Nancy');

CREATE TYPE reportsTo AS
   (employeeid varchar(50),
    depth integer);


--8.3 syntax
CREATE OR REPLACE FUNCTION fn_reportsTo (param_employeeid varchar(50), param_depth integer)  
RETURNS SETOF reportsTo
 AS  
 $$
 DECLARE
  var_managerid varchar(50);
  var_next_depth integer;
 BEGIN
    var_next_depth := param_depth + 1;
    SELECT managerid INTO var_managerid FROM employees WHERE employeeid = param_employeeid;
    IF FOUND AND var_managerid IS NOT NULL AND var_managerid > '' 
     AND param_depth < 6 AND var_managerid <> param_employeeid THEN
/***We stop if a person is their own manager or  a person has no manager or 
We've  exceeded a depth of 5 (to prevent potential infinite recursion ***/
        RETURN QUERY 
            SELECT employeeid, param_depth
                FROM employees M
                WHERE M.employeeid = param_employeeid
            UNION ALL
                SELECT employeeid, depth FROM fn_reportsTo(var_managerid, var_next_depth);
    ELSE 
        RETURN QUERY SELECT param_employeeid As employeeid, param_depth As depth;
    END IF;
 END;
$$
LANGUAGE 'plpgsql' STABLE;


SELECT *
    FROM fn_reportsTo('John');


 employeeid | depth
------------+-------
 John       |     0
 Nancy      |     1
 Peter      |     2
 Diana      |     3

Overloading Function names

PostgreSQL unlike Microsoft SQL Server, supports function name and argument overloading. Which in a nutshell means you can have multiple functions with the same name as long as they take
a different number of arguments and/or different argument types. The functions don’t even need to be written in the same language. With that said we can make our function a bit nicer by doing this.

CREATE OR REPLACE FUNCTION fn_reportsTo(param_employeeid varchar(50))
RETURNS SETOF reportsTo
AS
$$
    SELECT * FROM fn_reportsTo($1, 0);
$$
LANGUAGE 'sql' STABLE;

By doing the above we kill 2 birds with one stone:

1. We no longer have to pass in a goofy 0 for depth.
2. We can now use a unique feature of functions written in SQL and C that allows set returning functions in those languages to be used in the SELECT clause as described in our
   Trojan SQL Function Hack — A PL Lemma in Disguise

   Which allows us to do this:

   SELECT e.employeeid As emp, (fn_reportsTo(employeeid)).*
       FROM employees E
       ORDER BY e.employeeid, depth;
   
     emp  | employeeid | depth
   -------+------------+-------
    Diana | Diana      |     0
    John  | John       |     0
    John  | Nancy      |     1
    John  | Peter      |     2
    John  | Diana      |     3
    Nancy | Nancy      |     0
    Nancy | Peter      |     1
    Nancy | Diana      |     2
    Peter | Peter      |     0
    Peter | Diana      |     1
   

What is in store in 8.4

8.4 has a couple of things cooking to advance SQL in PostgreSQL and improved features for PL/PGSQL. Below is just a sampling.

1. David Fetters Trees and More http://fetter.org/Trees_and_More_WEST_2008.pdf — which demonstrates how to write recursive SQL statements with upcoming Common Table Expressions in 8.4
2. Hubert’s Waiting for 8.4 — pl/* srf functions in selects http://www.depesz.com/index.php/2008/11/03/waiting-for-84-pl-srf-functions-in-selects/ — this one
   is a pretty important one I think. It means no longer a need for a trojan like hack for 8.4+
3. Hubert’s Waiting for 8.4 — RETURNS TABLE http://www.depesz.com/index.php/2008/08/04/waiting-for-84-returns-table/
   Again this feature I consider to be pretty important. Especially for people coming from Microsoft SQL Server backgrounds from a comfort stand-point (look at how similar SQL Servers table returning functions look). It also means no longer needing to create a custom type as we
   did above if you want to return a custom set of fields.

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 19 for more information.

After level if any, you can write a format (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.