Database Reference
In-Depth Information
You can use the
%
sign to display variables in your messages as well.
The following function will get all records from the
warehouse_tbl
table, and if it
inds no rows, it will tell the user that the table is empty and also shows the variable's
stored value, which will be
0
in the case when no rows exist.
For the sake of explanation, let's drop any rows present in the table using the
following statement:
warehouse_db=# DELETE FROM warehouse_tbl;
Now, consider the following example using the
how_to_raise()
function in the
warehouse_tbl
table:
warehouse_db=# CREATE OR REPLACE FUNCTION how_to_raise()
RETURNS INTEGER AS $$
Declare
total INTEGER;
BEGIN
SELECT COUNT(*) INTO total FROM warehouse_tbl;
IF (total > 0) THEN
RETURN total;
ELSE
RAISE NOTICE 'table is empty, % , rows', total;
END IF;
END;
$$ LANGUAGE plpgsql;
Execute the function in
psql
and observe the results:
warehouse_db=# SELECT how_to_raise();
NOTICE: table is empty, 0, rows
ERROR: control reached end of function without RETURN
CONTEXT: PL/pgSQL function how_to_raise()
On the contrary, the
EXCEPTION
level will send the messages as
ERROR
to the client
program and will also abort the statement that triggers the exception.
Suppose there are no rows present in the previous example; we will enter a row and
raise an exception and then the statement will be aborted.
For the sake of explanation, let's drop any rows present in the table using the
following statement:
warehouse_db=# DELETE FROM warehouse_tbl;
