In PostgreSQL, we have access to the RAISE statement which allows us to build an error-handling logic. In this tutorial, we will learn how to use the RAISE NOTICE statement in PostgreSQL to display a message during the execution of a given SQL function or PL/pgSQL block.
PostgreSQL RAISE NOTICE Statement
This statement in PostgreSQL allows us to output the informational messages during the code execution. We can use these messages to debug or provide feedback during code execution.
Think of the RAISE NOTICE statement in PostgreSQL as printing or logging messages in other programming languages.
Syntax:
The following shows the syntax of the RAISE NOTICE statement in PostgreSQL:
The statement has three main parameters:
Level – The level parameter is optional. We can use it to specify the verbosity level of the notification message. The supported verbosity levels include:
- DEBUG
- LOG
- INFO
- NOTICE
- WARNING
- EXCEPTION
- ERROR
Message – This specifies the literal string or a format string which contains the notice message’s text. We can set the placeholders for values using the supported symbols which are similar to the printf function in C.
Expression – The expression parameters are optional. We can use them to specify the values to replace the placeholders that are defined in the input string.
Examples:
Here are some basic examples of working with the RAISE NOTICE statement in PostgreSQL.
Example 1: Basic Usage
The following example demonstrates how to use the RAISE NOTICE statement to create a notice message in a PostgreSQL function:
RETURNS void AS $$
BEGIN
RAISE NOTICE 'Hello world!!.';
END;
$$ LANGUAGE plpgsql;
In this example, we create a function called sample_func() that uses the RAISE NOTICE statement to print the “Hello world!!” message.
Example 2: Using the Placeholders
The RAISE NOTICE statement allows us to specify the placeholders using the “%” operator and replace them with values from a given expression.
An example is as follows:
RETURNS void AS $$
BEGIN
RAISE NOTICE 'Hello, %!', name;
END;
$$ LANGUAGE plpgsql;
In this case, we define a function called greet_user() that takes the name parameter. The RAISE NOTICE statement includes a placeholder (%) in the message string. This will be replaced with the value of the name parameter during printing.
Example 3: Specifying the Verbosity Level
The following example demonstrates how to specify a specific verbosity level in the RAISE NOTICE statement:
RETURNS void AS $$
DECLARE
error_message text;
BEGIN
-- code that will raise an error
BEGIN
-- Simulating an error
error_message := 'An error occurred';
RAISE EXCEPTION '%', error_message;
EXCEPTION
WHEN OTHERS THEN
-- Log the error as a notice
RAISE NOTICE 'Error: %', error_message;
END;
END;
$$ LANGUAGE plpgsql;
In this case, we have a function called log_error() that demonstrates the basic error handling. The function simulates an error by raising an exception. However, in the EXCEPTION block, we catch the error and use the RAISE NOTICE to log the error instead of re-raising it.
Example 4: Parameter Formatting
The RAISE NOTICE parameter also allows us to perform the value formatting, such as floating point values, as demonstrated in the following example:
RETURNS float AS $$
DECLARE
average float;
BEGIN
average := (SELECT AVG(num) FROM unnest(numbers) AS num);
RAISE NOTICE 'The average of the numbers is %.2f', average;
RETURN average;
END;
$$ LANGUAGE plpgsql;
In this example, we create a function called calculate_average() that takes an array of integers as input. The function then calculates the average of the provided values and uses the RAISE NOTICE statement to display the result with two decimal places.
The %.2f in the message string is a formatting specifier which instructs the PostgreSQL to format the average value as a floating-point number with two decimal places.
Conclusion
We explored how to use the RAISE NOTICE statement in PostgreSQL to display the informational messages during the execution of SQL functions, stored procedures, or PL/pgSQL blocks.
