Return to Answer

I really like Craig's explanation of the feature. The SQL-2011 Spec defines them in the context of a trigger as "a collection of rows being deleted, inserted or replaced is known as a transition table." A similar explanation is provided in the docs,

While transition tables for AFTER triggers are specified using the REFERENCING clause in the standard way, the row variables used in FOR EACH ROW triggers may not be specified in REFERENCING clause. They are available in a manner which is dependent on the language in which the trigger function is written. Some languages effectively behave as though there is a REFERENCING clause containing OLD ROW AS OLD NEW ROW AS NEW.

Essentially they make the whole statement's changes available to you, which is super handy. For reference, the DDL on create trigger looks like this with transitions tables

REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable

You can see an example here, and here is one from the test suite,

CREATE TABLE transition_table_base (id int PRIMARY KEY, val text);

CREATE FUNCTION transition_table_base_ins_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 newtable
           $q$ LOOP
    t = t || l || E'\n';
  END LOOP;

  RAISE INFO '%', t;
  RETURN new;
END;
$$;

CREATE TRIGGER transition_table_base_ins_trig
  AFTER INSERT ON transition_table_base
  REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable
  FOR EACH STATEMENT
  EXECUTE PROCEDURE transition_table_base_ins_func();

Some additional notes

1. They're only available on AFTER triggers.
2. They take into account things like ON CONFLICT.

It's important to point out that it's not entirely sure to be available in PG 10. There are lots of open issues with transition tables. Most have patches. There is some infighting which is kind of routine. It seems the heavy lifting was picked up by someone else. The thread indicates that we'll know soon.

Author responded - seems to be going well again,.

I really like Craig's explanation of the feature. The SQL-2011 Spec defines them in the context of a trigger as "a collection of rows being deleted, inserted or replaced is known as a transition table." A similar explanation is provided in the docs,

While transition tables for AFTER triggers are specified using the REFERENCING clause in the standard way, the row variables used in FOR EACH ROW triggers may not be specified in REFERENCING clause. They are available in a manner which is dependent on the language in which the trigger function is written. Some languages effectively behave as though there is a REFERENCING clause containing OLD ROW AS OLD NEW ROW AS NEW.

Essentially they make the whole statement's changes available to you, which is super handy. For reference, the DDL on create trigger looks like this with transitions tables

REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable

You can see an example here, and here is one from the test suite,

CREATE TABLE transition_table_base (id int PRIMARY KEY, val text);

CREATE FUNCTION transition_table_base_ins_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 newtable
           $q$ LOOP
    t = t || l || E'\n';
  END LOOP;

  RAISE INFO '%', t;
  RETURN new;
END;
$$;

CREATE TRIGGER transition_table_base_ins_trig
  AFTER INSERT ON transition_table_base
  REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable
  FOR EACH STATEMENT
  EXECUTE PROCEDURE transition_table_base_ins_func();

Some additional notes

1. They're only available on AFTER triggers.
2. They take into account things like ON CONFLICT.

It's important to point out that it's not entirely sure to be available in PG 10. There are lots of open issues with transition tables. Most have patches. There is some infighting which is kind of routine. It seems the heavy lifting was picked up by someone else. The thread indicates that we'll know soon.

Author responded - seems to be going well again,.

I really like Craig's explanation of the feature. The SQL-2011 Spec defines them in the context of a trigger as "a collection of rows being deleted, inserted or replaced is known as a transition table." A similar explanation is provided in the docs,

While transition tables for AFTER triggers are specified using the REFERENCING clause in the standard way, the row variables used in FOR EACH ROW triggers may not be specified in REFERENCING clause. They are available in a manner which is dependent on the language in which the trigger function is written. Some languages effectively behave as though there is a REFERENCING clause containing OLD ROW AS OLD NEW ROW AS NEW.

Essentially they make the whole statement's changes available to you, which is super handy. For reference, the DDL on create trigger looks like this with transitions tables

REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable

You can see an example here, and here is one from the test suite,

CREATE TABLE transition_table_base (id int PRIMARY KEY, val text);

CREATE FUNCTION transition_table_base_ins_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 newtable
           $q$ LOOP
    t = t || l || E'\n';
  END LOOP;

  RAISE INFO '%', t;
  RETURN new;
END;
$$;

CREATE TRIGGER transition_table_base_ins_trig
  AFTER INSERT ON transition_table_base
  REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable
  FOR EACH STATEMENT
  EXECUTE PROCEDURE transition_table_base_ins_func();

Some additional notes

1. They're only available on AFTER triggers.
2. They take into account things like ON CONFLICT.

It's important to point out that it's not entirely sure to be available in PG 10. There are lots of open issues with transition tables. Most have patches. There is some infighting which is kind of routine. It seems the heavy lifting was picked up by someone else. The thread indicates that we'll know soon.

I really like Craig's explanation of the feature. The SQL-2011 Spec defines them in the context of a trigger as "a collection of rows being deleted, inserted or replaced is known as a transition table." A similar explanation is provided in the docs,

While transition tables for AFTER triggers are specified using the REFERENCING clause in the standard way, the row variables used in FOR EACH ROW triggers may not be specified in REFERENCING clause. They are available in a manner which is dependent on the language in which the trigger function is written. Some languages effectively behave as though there is a REFERENCING clause containing OLD ROW AS OLD NEW ROW AS NEW.

Essentially they make the whole statement's changes available to you, which is super handy. For reference, the DDL on create trigger looks like this with transitions tables

REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable

You can see an example here, and here is one from the test suite,

CREATE TABLE transition_table_base (id int PRIMARY KEY, val text);

CREATE FUNCTION transition_table_base_ins_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 newtable
           $q$ LOOP
    t = t || l || E'\n';
  END LOOP;

  RAISE INFO '%', t;
  RETURN new;
END;
$$;

CREATE TRIGGER transition_table_base_ins_trig
  AFTER INSERT ON transition_table_base
  REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable
  FOR EACH STATEMENT
  EXECUTE PROCEDURE transition_table_base_ins_func();

Some additional notes

1. They're only available on AFTER triggers.
2. They take into account things like ON CONFLICT.

It's important to point out that it's not entirely sure to be available in PG 10. There are lots of open issues with transition tables. Most have patches. There is some infighting which is kind of routine. It seems the heavy lifting was picked up by someone else. The thread indicates that we'll know soon.

Author responded - seems to be going well again,.

I really like Craig's explanation of the feature. The SQL-2011 Spec defines them in the context of a trigger as "a collection of rows being deleted, inserted or replaced is known as a transition table." Another nice expansion is provided in the docs,

While transition tables for AFTER triggers are specified using the REFERENCING clause in the standard way, the row variables used in FOR EACH ROW triggers may not be specified in REFERENCING clause. They are available in a manner which is dependent on the language in which the trigger function is written. Some languages effectively behave as though there is a REFERENCING clause containing OLD ROW AS OLD NEW ROW AS NEW.

Essentially they make the whole statement's changes available to you, which is super handy. For reference, the DDL on create trigger looks like this with transitions tables

REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable

You can see an example here, and here is one from the test suite,

CREATE TABLE transition_table_base (id int PRIMARY KEY, val text);

CREATE FUNCTION transition_table_base_ins_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 newtable
           $q$ LOOP
    t = t || l || E'\n';
  END LOOP;

  RAISE INFO '%', t;
  RETURN new;
END;
$$;

CREATE TRIGGER transition_table_base_ins_trig
  AFTER INSERT ON transition_table_base
  REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable
  FOR EACH STATEMENT
  EXECUTE PROCEDURE transition_table_base_ins_func();

Some additional notes

1. They're only available on AFTER triggers.
2. They take into account things like ON CONFLICT.

It's important to point out that it's not entirely sure to be available in PG 10. There are lots of open issues with transition tables. Most have patches. There is some infighting which is kind of routine. It seems the heavy lifting was picked up by someone else. The thread indicates that we'll know soon.

I really like Craig's explanation of the feature. The SQL-2011 Spec defines them in the context of a trigger as "a collection of rows being deleted, inserted or replaced is known as a transition table." A similar explanation is provided in the docs,

While transition tables for AFTER triggers are specified using the REFERENCING clause in the standard way, the row variables used in FOR EACH ROW triggers may not be specified in REFERENCING clause. They are available in a manner which is dependent on the language in which the trigger function is written. Some languages effectively behave as though there is a REFERENCING clause containing OLD ROW AS OLD NEW ROW AS NEW.

Essentially they make the whole statement's changes available to you, which is super handy. For reference, the DDL on create trigger looks like this with transitions tables

REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable

You can see an example here, and here is one from the test suite,

CREATE TABLE transition_table_base (id int PRIMARY KEY, val text);

CREATE FUNCTION transition_table_base_ins_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 newtable
           $q$ LOOP
    t = t || l || E'\n';
  END LOOP;

  RAISE INFO '%', t;
  RETURN new;
END;
$$;

CREATE TRIGGER transition_table_base_ins_trig
  AFTER INSERT ON transition_table_base
  REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable
  FOR EACH STATEMENT
  EXECUTE PROCEDURE transition_table_base_ins_func();

Some additional notes

1. They're only available on AFTER triggers.
2. They take into account things like ON CONFLICT.

It's important to point out that it's not entirely sure to be available in PG 10. There are lots of open issues with transition tables. Most have patches. There is some infighting which is kind of routine. It seems the heavy lifting was picked up by someone else. The thread indicates that we'll know soon.