I would like to utilize different timestamps within a Postgres function. Due to the fact that Postgres functions are executed in a transaction, I am unsure how to achieve this. If I use NOW() or CURRENT_TIMESTAMP() at different points within a function, they will all return the exact same result. Please see example below.
I have seen this accomplished before, so I am fairly certain it is possible. But I don't recall how the code was written.
Example:
CREATE TABLE "temp".foo (col timestamp(3));
CREATE OR REPLACE FUNCTION "temp".foo_ins()
RETURNS int
LANGUAGE plpgsql
AS $function$
BEGIN
INSERT INTO "temp".foo (col) VALUES (now());
PERFORM pg_sleep(3);
INSERT INTO "temp".foo (col) VALUES (now());
RETURN 0;
END $function$;
SELECT "temp".foo_ins();
SELECT *
FROM "temp".foo;
Results:
2022-08-04 09:16:27.561
2022-08-04 09:16:27.561
