To aid me with debugging another issue in my database, I've written the following function as a trigger in postgresql:
CREATE OR REPLACE FUNCTION stage.triggerlogfunction()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
begin
insert into stage.temptriggerlog_complete
values
(current_timestamp, 'Hello');
perform pg_sleep(5);
insert into stage.temptriggerlog_complete
values
(current_timestamp, 'Did');
perform pg_sleep(5);
insert into stage.temptriggerlog_complete
values
(current_timestamp, 'This Work?');
return null;
END;
$function$
;
When a new row is inserted into a table in my database, this trigger is set to fire. This works as expected, but what I was expecting to see when this ran was three rows with timestamps that are 5 seconds apart (running in sequence with a 5 second delay between each), but the actual result I had returned was three rows all with the same timestamp.
Why is this happening? Is there a way I can force the behaviour that I was expecting?
CodePudding user response:
is there any way I can get the current timestamp at the time each insert statement runs?
You're looking for statement_timestamp()
(which would be the timestamp of the statement causing your trigger to fire) or even clock_timestamp()
then.
See the documentation for what they do and the alternatives.