PL/pgSQL trigger and sequence increment

Поиск
Список
Период
Сортировка
От jonesd@xmission.com
Тема PL/pgSQL trigger and sequence increment
Дата
Msg-id 20110824130135.tbp3eqilckg8og4o@webmail.xmission.com
обсуждение исходный текст
Ответы Re: PL/pgSQL trigger and sequence increment  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Merlin Moncure
Дата:
Сообщение: Re: What is postgresql status?
Следующее
От: Pete Wall
Дата:
Сообщение: Re: Problem with 8.3.14 Windows binaries