Use NEW in FROM clause in Postgres trigger?

Question

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.

• ypercubeᵀᴹ: Please provide the CREATE TABLE statement and the whole trigger code.
• Evan Carroll: What is producing the array to begin with? This sounds like a half-baked optimization because you don't want to run multiple insert statements?
• Brylie Christopher Oxley: The array is coming from backend code. We have a lot of JSONB / JSONB[] columns in our database, which I would like to move to a more relational model.
• user1822: jsonb[] almost never makes sense. It's much better to use jsonb and store a proper JSON array inside the json value
• Brylie Christopher Oxley: Duly noted. I can't change the past decision here, but am trying to migrate the data to a new table moving forward.

Answer 1

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->>'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):

• Can PostgreSQL have a uniqueness constraint on array elements?

• Brylie Christopher Oxley: As requested, I have added the table definition, trigger, and trigger function to the original question. The goal here is a rolling migration, so we can deprecate this jsonb[] field with minimal downtime or data inconsistency.
• Brylie Christopher Oxley: I will also need to get the id field from the NEW row while unnesting the JSON data, so that I can create a ForeignKey relationship.
• Erwin Brandstetter: I updated accordingly. Details about ID are still fuzzy.
• Brylie Christopher Oxley: Thanks for your help and good humor. I have added a CREATE TABLE statement example in the question for the many_to_one_table.
• Brylie Christopher Oxley: We don't currently DELETE rows from the parent_table to my knowledge. For the UPDATE case, I was thinking to DELETE matching rows (by ForeignKey on UUID field) and re-creating them from the jsonb_array_field, to prevent duplicates.