Database Reference
In-Depth Information
Hmm. It seems we need to return something from the function even though it is
not needed for our purposes. The function definition says
CREATE FUNCTION …
RETURNS trigger
but we definitely cannot return a trigger from a function.
Back to the documentation!
OK, here it is. The trigger needs to return a value of a
ROW
or
RECORD
type and it is
ignored in
AFTER
triggers. For now, let's just return
NEW
as this is the right type and
always present even though it will be
NULL
in the
DELETE
trigger:
CREATE OR REPLACE FUNCTION notify_trigger()
RETURNS TRIGGER AS $$
BEGIN
RAISE NOTICE 'Hi, I got % invoked FOR % % %
on %',
TG_NAME,
TG_LEVEL,
TG_WHEN, TG_OP, TG_TABLE_NAME;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
We could have equally well used
RETURN NULL;
here as the return value of
AFTER
triggers is ignored anyway:
A new test:postgres=# INSERT INTO notify_test
VALUES(1),(2);
NOTICE: Hi, I got notify_insert_trigger
invoked FOR ROW AFTER INSERT on notify_test
NOTICE: Hi, I got notify_insert_trigger
invoked FOR ROW AFTER INSERT on notify_test
INSERT 0 2
As we see, the trigger function is indeed called once for each row inserted, so let's
use the same function to also report
UPDATE
and
DELETE
functions:
