Greetings. I noticed an interesting behavior when using a PL/pgSQL
trigger. I'm running PostgreSQL 8.3. The trigger function checks a
newly inserted or updated row for a type of uniqueness. Specifically,
each row in the table has a submitter id and an entry timestamp. No
two rows can have the same submitter id and entry timestamp month
(basically, this means that there can be one entry per submitter per
month). In other words, the trigger function is along the lines of:
BEGIN
IF NOT EXISTS (SELECT * FROM table_entry WHERE submitter_id =
new.submitter_id AND date_trunc('month',entry_timestamp) =
date_trunc('month',new.entry_timestamp)) THEN RETURN new;
ELSE RETURN NULL;
END IF;
END
Each row in the table also has a SERIAL identifier with a sequence
providing values. I'd like to provide information to the user
regarding why the INSERT or UPDATE failed, as the examples in the
documentation do via using a RAISE EXCEPTION instead of RETURN NULL
(see
http://www.postgresql.org/docs/8.3/interactive/plpgsql-trigger.html,
which appears to be unchanged in the documentation for 9.0). However,
if I do so, the sequence increments after the attempted INSERT or
UPDATE, which is not desired (and does not happen if RETURN NULL is
the result of the trigger function).
Any assistance is appreciated - thanks in advance!
---
Dominic Jones, Ph.D.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
