Use NEW in FROM clause in Postgres trigger?
NEW and OLDin trigger functions are records - each only defined where applicable. (So no OLD in an INSERT trigger, for instance.) You can perform ROW operations on them. Reference nested columns with dot notation (like table-qualified column names) like they were tables included in the FROM list. NEW and OLD are visible almost everywhere in SQL DML statements in a trigger function. (Except dynamic SQL with EXECUTE.)

To treat NEW as actual table you'd have to transform it first - which is typically not necessary. Like: (SELECT NEW.*). For a meaningful use case see chapter 3. in this related answer:


PostgreSQL use NEW in query for INSTEAD OF trigger


Your basic trigger function burns down to:

CREATE OR REPLACE FUNCTION unnest_and_normalize_json()
  RETURNS trigger AS 
$func$
BEGIN
   INSERT INTO many_to_one_table (parent_table_id, subfield_a, subfield_b)
   SELECT NEW.id
        , ja-&gt;&gt;'subfieldA' AS subfield_a  -- column alias only for documentation
        , ja-&gt;&gt;'subfieldB'
   FROM   unnest(NEW.jsonb_array_field) ja;  -- produces a derived table

   RETURN NEW; -- optional for AFTER trigger
END
$func$  LANGUAGE plpgsql;


This was just noise:

if NEW.jsonb_array_field is null then ...


The column jsonb_array_field is defined as:

jsonb_array_field jsonb[] NOT NULL DEFAULT '{}'::jsonb[],


It's an AFTER trigger, the NOT NULL constraint would already have raised an exception for NULL in jsonb_array_field at this point.

You can merge your two triggers into one:

CREATE TRIGGER unnest_and_normalize_json_on_insert
AFTER INSERT OR UPDATE ON parent_table  -- !
FOR EACH ROW EXECUTE PROCEDURE unnest_and_normalize_json();


That said, you'd have to do a lot more to cover UPDATE and DELETE properly. Similar to this one (chapter The dark side):


Can PostgreSQL have a uniqueness constraint on array elements?