Thread:

Could you please confirm whether this behavior of PostgreSQL is a bug or a
feature?

Issue occurs wirh    PostgreSQL 7.3.3
compiled with         GCC 3.2.3
on a            Celeron Coppermine 1100 MHz
powerd by         Linux 2.4.21


I have two functions, one is called by a trigger, the other by user. Both
are written in PL/pgSQL.

The trigger fires after an INSERT on a table. The triggerd function requires
the max value of the SEQUENCE of that table. (SELECT INTO max last_value
FROM table_ID_seq;)

This works quite excellent as long as I execute common INSERTS.

Now, the user-called function executes several INSERTS at once, and the
trigger also is fired several times, but AFTER the complete function is done, and
not after each specific INSERT, as I expected.

This confuses me since my trigger was created with FOR EACH ROW.

This behavior leads to the following problem:

After the function made e.g. 5 INSERTS, the trigger called function is
triggered 5 times at once.
Each instance of the function now gets

n+5 n+5 n+5 n+5 n+5

instead of

n+1 n+2 n+3 n+4 n+5

as supposed.

Is the described behavior a bug or a feature?
Any recommendations?

If its a feature, can u give me a short explaination why its done that way?

Regards,

Joacher

--
COMPUTERBILD 15/03: Premium-e-mail-Dienste im Test
--------------------------------------------------
1. GMX TopMail - Platz 1 und Testsieger!
2. GMX ProMail - Platz 2 und Preis-Qualitätssieger!
3. Arcor - 4. web.de - 5. T-Online - 6. freenet.de - 7. daybyday - 8. e-Post

joacher@gmx.de writes:
> Now, the user-called function executes several INSERTS at once, and
> the trigger also is fired several times, but AFTER the complete
> function is done, and not after each specific INSERT, as I expected.

AFTER triggers fire at the end of the SQL statement.

Presently this is taken to mean "at the end of the complete interactive
statement", ie, just before we go back to waiting for client input.
There has been some discussion of changing it so that when an INSERT
(resp. UPDATE or DELETE) is done within a function, any triggers fired
are executed at completion of that statement before proceeding with the
function.  We've not made the change yet though.

            regards, tom lane