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. Regards, Miroslav Simulcik