PL/pgSQL trigger and sequence increment

From: jonesd(at)xmission(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: PL/pgSQL trigger and sequence increment
Date: 2011-08-24 19:01:35
Message-ID: 20110824130135.tbp3eqilckg8og4o@webmail.xmission.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Raymond O'Donnell 2011-08-24 19:08:05 Re: What is postgresql status?
Previous Message Peter Eisentraut 2011-08-24 18:59:13 Re: [GENERAL] documentation suggestion