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?

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();

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';