PostgreSQL 10 High Performance - Third Edition

Transition tables for trigger

Starting from PostgreSQL 10, this feature is available. Transition tables for triggers are tables with old and new tuples, so your triggers can see what changed; they make the whole statement's changes available to you. Here is an example taken from the test suite at https://github.com/postgres/postgres/blob/a571c7f661a7b601aafcb12196d004cdb8b8cb23/src/test/regress/sql/plpgsql.sql:

CREATE TABLE transition_table_base (id int PRIMARY KEY, val text); CREATE OR REPLACE FUNCTION transition_table_base_upd_func() RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE t text; l text; BEGIN t = ''; FOR l IN EXECUTE $q$ EXPLAIN (TIMING off, COSTS off, VERBOSE on) SELECT * FROM oldtable ot FULL JOIN newtable nt USING (id) $q$ LOOP ...