Statement level trigger clarification

Chris Velevitch wrote:

I'm new to triggers and I'm having difficulty in understanding how
statement level triggers on before updates work.

I have a function that sets new.last_modified := current_timestamp;

If I were to define a trigger as:-

CREATE TRIGGER my_trigger
BEFORE INSERT OR UPDATE
ON my_table
FOR EACH STATEMENT
EXECUTE PROCEDURE my_function();

and my update statement were to update more than one row, would I be
correct in understanding that every row the update statement touches
will have the exact same value for last_modified?

No, this is not going to work at all. NEW and OLD are not available in
statement triggers. So your function will fail.

Peter Eisentraut wrote:

Chris Velevitch wrote:

I have a function that sets new.last_modified := current_timestamp;

Remember that current_timestamp is stable across the lifetime of a
transaction; it'll return the same value each time it is called. Given
that, you can just use it in a row-level trigger, knowing that a batch
of records updated by the same transaction (which to the database is "at
the same time") will get the same timestamp.

There are also time functions that return the real wall clock, and the
clock at the time of the start of the statement rather than the
transaction. See:

http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

You do still pay the overhead of invoking a PL/PgSQL trigger for each
record altered/inserted, but there doesn't seem to be much of a way
around that. It's an area where a `DEFAULT FORCE' qualifier would be
nice - something to ignore user-submitted input and overwrite it with
the default parameter, probably a function call. At present there's
nothing like that (as far as I know) so you need to use a row-level trigger.

--
Craig Ringer