Database Reference
In-Depth Information
General error reporting and error handling
If you want to provide status to the user during your execution, you will become fa-
miliar with the commands
RAISE
,
NOTICE
, and
NOTIFY
. From a transactional per-
spective, the difference is that
RAISE NOTICE
will send the message immediately,
even when wrapped in a transaction, while
NOTIFY
will wait for the transaction to
settle before sending a message.
NOTIFY
will therefore not actually notify you of
anything if the transaction fails and rolled back.
User-defined functions (UDF)
The ability to write user-defined functions is the powerhouse feature of PostgreSQL.
Functions can be written in many different programming languages, use any kind
of control structures that the language provides, and in the case of "untrusted" lan-
guages, can perform any operation that is available in PostgreSQL.
Functions can provide features that are not even directly SQL related. Some of the
upcoming examples will show how to get network address information, query the
system, move files around, and just about anything your heart desires.
So, how do we access this sugary goodness of PostgreSQL? We start by declaring
that we want a function:
CREATE OR REPLACE FUNCTION addition (integer,
integer) RETURNS integer
AS $$
DECLARE retval integer;
BEGIN
SELECT $1 + $2 INTO retval;
RETURN retval;
END;
$$ LANGUAGE plpgsql;
But what if we wanted to add three integers together?
CREATE OR REPLACE FUNCTION addition (integer,
integer, integer) RETURNS integer
