Now() should always return the same, very first, result...
On Wednesday, February 6, 2013, Miroslav Šimulčík wrote:
Hi all,
I have deferred constraint update trigger in which I need to set same timestamp to all modified rows. The time needs to be the time of first invocation of this trigger fuction in transaciton. My intention is to set commit time to rows modified in transaction.
So I need function that will store and return given timestamp on first call in transaction and on subsequent calls will return stored timestamp. This function have to be as fast as possible to minimize the inpact on performance of trigger.
I have created a plpgsql function that uses temporal table for this task. On first invocation in transaction row with timestamp is inserted and on commit deleted. What I don't like is overhead with checks on table existence on each invocation. Here is code:
CREATE OR REPLACE FUNCTION get_my_timestamp (
IN in_initial_timestamp TIMESTAMPTZ
) RETURNS TIMESTAMPTZ AS
$$
DECLARE
v_ret TIMESTAMPTZ;
BEGIN
--check temp table existence
PERFORM
1
FROM
pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace
WHERE
c.relkind IN ('r','') AND
c.relname = 'timestamp_storage' AND
pg_catalog.pg_table_is_visible(c.oid) AND
n.nspname LIKE 'pg_temp%';
IF NOT FOUND THEN
CREATE TEMP TABLE timestamp_storage (
my_timestamp TIMESTAMPTZ
) ON COMMIT DELETE ROWS;
END IF;
--select timestamp
SELECT
my_timestamp
INTO
v_ret
FROM
timestamp_storage;
IF NOT FOUND THEN
INSERT INTO timestamp_storage(my_timestamp)
VALUES (in_initial_timestamp)
RETURNING my_timestamp
INTO v_ret;
END IF;
RETURN v_ret;
END;
$$ LANGUAGE plpgsql;
Example:
begin;
select get_my_timestamp(clock_timestamp());
get_my_timestamp
----------------------------
2013-02-06 11:07:33.698+01
select get_my_timestamp(clock_timestamp());
get_my_timestamp
----------------------------
2013-02-06 11:07:33.698+01
commit;
select get_my_timestamp(clock_timestamp());
get_my_timestamp
----------------------------
2013-02-06 11:09:02.406+01
Is there any more effective way of accomplishing this? Maybe in different language.
