Postgresql – Use NEW in FROM clause in Postgres trigger

postgresqlpostgresql-9.4subquerytrigger

I am trying to write a Postgres trigger to unnest an array field before inserting or updating a new row. E.g.

SELECT
unnest(something)
FROM NEW

However, this seems to lead to an error:

relation 'new' does not exist

How can I use the NEW row inside of the trigger function, in such a way that allows me to unnest an array field for further processing?

CREATE TABLE statements

Below are examples of the table structures:

CREATE TABLE parent_table (
    id uuid NOT NULL,
    jsonb_array_field jsonb[] NOT NULL DEFAULT '{}'::jsonb[],
    CONSTRAINT "parent_table_pkey" PRIMARY KEY (id),
);

CREATE TABLE many_to_one_table (
  id serial primary key,
  parent_table_id uuid references parent_table(id),
  subfield_a text,
  subfield_a text
);

TRIGGER function

Here is the essence of the TRIGGER function:

CREATE OR REPLACE FUNCTION unnest_and_normalize_json() RETURNS TRIGGER AS 
$body$
begin
    if NEW.jsonb_array_field is null then
        raise exception 'jsonb_array_field is null';
    else
        insert into 
            many_to_one_table(subfield_a, subfield_b)
            select
                parent_table_id, -- this is to be the ForeignKey
                json_data->>'subfieldA' as subfield_a,
                json_data->>'subfieldB' as subfield_b
            from (
                select 
                    id, -- need ID for ForeignKey relationship
                    unnest(jsonb_array_field) as json_data
                    from new
            ) as unnested_json;
    end if;
    RETURN new;
end;
$body$ 
LANGUAGE plpgsql;

TRIGGER statement

The trigger statement can be run either before or after INSERT and UPDATE, so long as the data are mirrored in the 'migration' table.

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

CREATE TRIGGER unnest_and_normalize_json_on_update AFTER UPDATE ON parent_table
FOR EACH ROW EXECUTE PROCEDURE unnest_and_normalize_json();

ForeignKey clarification
We are trying to transition this part of our data model to use a ForeignKey relationship rather than JSON field. The trigger is intended as a temporary step to ensure the normalized_table has data going forward, while we backfill/migrate data from older records.

Best Answer

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:

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->>'subfieldA' AS subfield_a  -- column alias only for documentation
        , ja->>'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):

Related Question