Database Reference
In-Depth Information
RAISE EXCEPTION '%', TG_ARGV[0];
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
This function just raises an exception with the string passed as an argument in the
CREATE TRIGGER
statement. Notice that we cannot use
TG_ARGV[0]
directly, as
the message as the PL/pgSQL syntax requires a string constant as the third element
of
RAISE
.
Using the previous trigger function, we can set up triggers to enforce various con-
straints by specifying both the condition (in the
WHEN(...)
clause) and the message
to raise if this condition is met as the argument to trigger function:
CREATE TRIGGER no_updates_on_friday_afternoon
BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE
ON new_tasks
FOR EACH STATEMENT
WHEN (CURRENT_TIME > '12:00' AND extract(DOW
from CURRENT_TIMESTAMP) = 5)
EXECUTE PROCEDURE cancel_with_message('Sorry,
we have a "No task change on Friday afternoon"
policy!');
Now if anybody tries to modify the
new_tasks
table on any Friday afternoon he gets
a message about this policy:
postgres=# insert into new_tasks values (...);
ERROR: Sorry, we have a "No task change on
Friday afternoon" policy!
Note
One thing to note about trigger arguments is that the argument list is always an
array of text (
text[]
).
