How to access to other INSERTed/UPDATEd rows from trigger in PostgreSQL?

Question

Solution in MS SQL

MS SQL trigger functions have deleted and inserted system tables in which all rows affected by operation are stored. You can count updated rows:

set @updatedCount = (select count(*) from deleted)

or find out minimum value:

set @updatedMinimumCol1 = (select min(col1) from deleted)

---

Problem with PostgreSQL

For FOR EACH ROW triggers I can use OLD and NEW system records, but they store only 1 row for each call of trigger. Calls of trigger are separated, so if user updates 10 rows, trigger will be called 10 times, but each time I can know only about 1 current row, not about all 10 rows.

For FOR EACH STATEMENT I do not know any mechanism of access to updated rows at all. I use PostgreSQL v9.6, OLD TABLE and NEW TABLE were introduced in v10.

PostgreSQL does not allow the old and new tables to be referenced in statement-level triggers, i.e., the tables that contain all the old and/or new rows, which are referred to by the OLD TABLE and NEW TABLE clauses in the SQL standard.

---

Try with transaction_timestamp() additional column

I can add special column with DEFAULT transaction_timestamp() to the main table and then use it to distinguish just-now-updated rows from other, but it is not a solution since multiple INSERTs/UPDATEs can be in one transaction and they will have the same timestamp of transaction. Probably I could clear this timestamp column in trigger after each statement to avoid this problem, but how to do this if such clearing will emit update trigger again - will be infinite update trigger calling.

So, this try was failed.

---

Bad solution in PostgreSQL

The only way I know is that:

First, use FOR EACH ROW trigger to collect current statistics (min and count) like aggregate functions. I use temp table to store it between calls (this trigger gets called 1 time for each row). But we will not know which row is last (when time will come to use this statistics).

CREATE TEMP TABLE IF NOT EXISTS _stats (
 _current_min int,
 _current_count int
) ON COMMIT DROP;
IF EXISTS(SELECT 1 FROM _stats LIMIT 1) THEN
 --Current row is not first, there is statistics for previous rows.
 UPDATE _stats
 SET _current_min = (CASE WHEN NEW.col1 < _current_min THEN NEW.col1
 ELSE _current_min END)
 , _current_count = _current_count + 1;
ELSE
 --There is no stats because current row is first for this INSERT/UPDATE
 INSERT INTO _stats (_current_min, _current_count)
 VALUES (NEW.col1, 1);
END IF;

Second, use FOR EACH STATEMENT trigger to use collected statistics. Do not forget to clear temp table (if user will run multiple INSERTs/UPDATEs in one transaction, old statistics will remain in temp table and corrupt all next calculations!).

For more complex tasks we can create temp tables inserted and deleted on the same way as _stats.

---

The workaround

In PostgreSQL we can use RETURNING clause for INSERT/UPDATE/DELETE to obtain new values of all rows affected by operation. Then we can manipulate with them, but each function with INSERTs/UPDATEs have to implement this technology ===> 1. additional code in functions with such INSERTs/UPDATEs - duplication of the RETURNING; 2. we can forget to implement such technology to new function; 3. data will be corrupted since required manipulations will not be called automatically (like triggers will).

---

The question

Maybe, you know a better way to access all rows affected by INSERT/UPDATE?

• Evgeny Nozdrev: Found an error in "transaction_timestamp() additional column" method. Now both methods works. I will add them as answer later. But maybe somebody know more elegant answer?

Answer 1

See the docs, you should be able to access the old and new records from a statement trigger:

CREATE TRIGGER some_table_update_trigger
 AFTER UPDATE ON some_table
 REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab
 FOR EACH STATEMENT
 EXECUTE PROCEDURE do_something_with_newtab_and_oldtab();

• Evgeny Nozdrev: We use PostgreSQL v9.6. This feature was introduced in v10. But your answer will be very helpful for others))

Answer 2

For PostgreSQL 10+ see @ewramner's answer.

For lower versions I found 2 solutions. Both works only if you wish to use inserted and deleted tables in AFTER trigger.

---

Solution 1. Temp tables _inserted and _deleted.

First, in BEFORE FOR EACH ROW trigger create temp tables and fill them:

CREATE TRIGGER trigger_fill_sys_tables
 BEFORE INSERT OR UPDATE OR DELETE
 ON public.ttest2
 FOR EACH ROW
EXECUTE PROCEDURE public.tr_fill_sys_tables();
CREATE OR REPLACE FUNCTION public.tr_fill_sys_tables()
 RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
 EXECUTE 'CREATE TEMP TABLE IF NOT EXISTS _deleted (LIKE ' || tg_table_schema || '.' || tg_relname || ');';
 IF tg_op <> 'INSERT' THEN
 INSERT INTO _deleted
 SELECT old.*;
 END IF;
 EXECUTE 'CREATE TEMP TABLE IF NOT EXISTS _inserted (LIKE ' || tg_table_schema || '.' || tg_relname || ');';
 IF tg_op <> 'DELETE' THEN
 INSERT INTO _inserted
 SELECT new.*;
 END IF;
 IF tg_op <> 'DELETE' THEN
 RETURN new;
 ELSE
 RETURN old;
 END IF;
END;
$$;

Via new and old system records we have access to the current record each time trigger gets called. But when it gets called for 1st row, we can't know about 2nd row. We do not know if more rows exists at all. That's why

second. In AFTER EACH STATEMENT trigger all rows already collected. You can use this tables:

CREATE TRIGGER trigger_use_sys_tables
 AFTER INSERT OR UPDATE OR DELETE
 ON ttest2
 FOR EACH STATEMENT
EXECUTE PROCEDURE public.tr_use_sys_tables();
CREATE OR REPLACE FUNCTION public.tr_use_sys_tables()
 RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
 _row record;
BEGIN
 --If 0 rows was affected by statement, tr_fill_sys_tables() will NOT be called, _inserted will NOT be created. To avoid a crash, check it:
 IF NOT EXISTS(SELECT 1
 FROM pg_class
 WHERE relname = '_inserted') THEN
 RETURN NULL;
 END IF;
 --Work with sys tables.
 --Note: changing data in them will not affect to main table!
 --Note: changing data in main table can fire this trigger again and fall into infinity loop. CREATE TEMP TABLE _lock() before UPDATE and DROP it after one to check if trigger was called recursively.
 FOR _row IN
 SELECT
 COALESCE(n.id, o.id) AS id
 , o.data AS old_data
 , n.data AS new_data
 FROM _inserted n
 FULL OUTER JOIN _deleted o ON n.id = o.id
 LOOP
 RAISE NOTICE 'id = %, old data = %, new data = %', _row.id, _row.old_data, _row.new_data;
 END LOOP;
 --DO NOT FORGET to drop the tables!
 --Just clear is not a solution, since next INSERT/UPDATE/DELETE can work with another table with different structure
 DROP TABLE _deleted;
 DROP TABLE _inserted;
 RETURN NULL;
END;
$$;

---

Solution 2. Additional column in the table.

Good if you want to update inserted/updated strings again in trigger. Does not work for DELETE triggers, see solution 1.

First, add column trans_timest timestamp to the main table.

Second, write transaction_timestamp() to it via BEFORE FOR EACH ROW triggers:

CREATE TRIGGER trigger_trans_mark
 BEFORE INSERT OR UPDATE
 ON public.ttest
 FOR EACH ROW
EXECUTE PROCEDURE public.tr_ttest_trans_mark();
CREATE OR REPLACE FUNCTION public.tr_ttest_trans_mark()
 RETURNS trigger AS $$
BEGIN
 IF tg_op = 'INSERT' THEN --to not crash when checking "old" record
 new.trans_timest = transaction_timestamp();
 ELSE
 IF old.trans_timest IS NULL THEN --if we are clearing marks, do not set them again
 new.trans_timest = transaction_timestamp();
 END IF;
 END IF;
 RETURN new;
END;
$$
LANGUAGE 'plpgsql';

Third, in AFTER FOR EACH STATEMENT you can use this mark to distinguish rows affected by this INSERT/UPDATE from others. Do not forget to clear marks in this trigger (if user executes multiple INSERTs/UPDATEs in one transaction, all they will have the same trans_timest and will be mixed). But you can clear this marks only if they are not cleared yet (if you call UPDATE in UPDATE trigger, it will call itself - without this check you will fall into infinity loop):

CREATE TRIGGER trigger_use_mark
 AFTER INSERT OR UPDATE
 ON public.ttest
 FOR EACH STATEMENT
EXECUTE PROCEDURE public.tr_ttest_use_mark();
CREATE OR REPLACE FUNCTION public.tr_ttest_use_mark()
 RETURNS trigger AS $$
BEGIN
 IF NOT EXISTS(SELECT 1
 FROM public.ttest t
 WHERE t.trans_timest = transaction_timestamp()
 LIMIT 1) THEN --To avoid infinity loop
 RETURN NULL;
 END IF;
 --Work with marked rows.
 ...
 --DO NOT FORGET to clear marks!
 UPDATE public.ttest
 SET trans_timest = NULL --update this rows again only simultaniously with clearing of marks!
 WHERE trans_timest = transaction_timestamp();
 RETURN NULL;
END;
$$
LANGUAGE 'plpgsql';