How to generate a single new row from a multi-row update in a trigger?

Question

Basically I have a few different tables which share an array field job_ids and each has their own status which is also an integer. On the job table, id is its primary key.

Postgres version 12.5

Table table1:

CREATE TABLE table1 (
id serial NOT NULL,
jobs_ids _int4 NULL,
status int4 NULL,
CONSTRAINT cable_pkey PRIMARY KEY (id)

Table job:

CREATE TABLE job (
id serial NOT NULL,
description varchar NULL,
created_at timestamptz NULL DEFAULT now(),
modified_at timestamptz NULL DEFAULT now(),
CONSTRAINT job_pkey PRIMARY KEY (id)

My trigger function:

CREATE OR REPLACE FUNCTION new_job_id_table1()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN

if new.status= 3 and new.jobs_ids = '{}' then 
   insert into job (description, type_id)
   select 'automated jobid with id=' || new.id, 1;

   new.jobs_ids := array[ max(b.id) ] from job b
   where new.jobs_ids = '{}' and b.type_id  = 1;
end if;
RETURN NEW; 

END;
$function$;

Trigger, set to run before update:

create trigger job_id_trigger_table1
before update of status on table1
for each row execute function new_job_id_table1();

Users update stuff through qgis but usually it's:

update table1 set status = 3 where id = $whatever_id_they_picked;

It creates a new entry in the job table and assigns that id to the updated row in table1.

It works fine if the user updates one row in table1 to status 3. However, it does nothing when a user updates multiple rows at a time.
If multiple rows are updated at once, I would like to generate only one job id for to several rows in table1 instead of (currently) multiple.

How to make the trigger work with multi-row updates?

Answer 1

Setup

First I simplified and improved your test setup:

CREATE TABLE table1 (
  id        serial PRIMARY KEY
, jobs_ids  int4[]
, status    int4 NOT NULL  -- !
);

-- DROP TABLE job;
CREATE TABLE job (
  id           serial PRIMARY KEY
, type_id      int NOT NULL DEFAULT 1  -- was missing
, description  varchar
, created_at   timestamptz DEFAULT now()
, modified_at  timestamptz DEFAULT now()
);

Also straightened out your original trigger to understand what's going on. You might use this as fallback if the solution below is not for you:

db<>fiddle here

Solution

Using an AFTER trigger with transition tables.

Trigger function:

CREATE OR REPLACE FUNCTION job_id_trigger_table1()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   WITH sel AS (
      SELECT n.id
      FROM   updated_new n
      JOIN   updated_old o USING (id)  -- never update id!
      WHERE  n.status = 3
      AND    o.status <> 3
      AND    n.jobs_ids = '{}'
      )
   , ins AS (
      INSERT INTO job (description, type_id)
      SELECT 'automated jobid for IDs ' || array_agg(s.id)::text, 1
      FROM   sel s
      HAVING count(*) > 0  -- skip all if no matching rows
      RETURNING id
      )
    UPDATE table1 t
    SET    jobs_ids = ARRAY[i.id]
    FROM   sel s, ins i
    WHERE  t.id = s.id;
      
   RETURN NULL;  -- for AFTER trigger
END
$func$;

Trigger:

CREATE TRIGGER job_id_trigger_table1
AFTER UPDATE ON table1
REFERENCING OLD TABLE AS updated_old
            NEW TABLE AS updated_new
FOR EACH STATEMENT
WHEN (pg_trigger_depth() = 0)  -- only immediate update!
EXECUTE FUNCTION job_id_trigger_table1();

db<>fiddle here

This creates a single row in job for any number of (qualifying!) rows updated in a single command.

I inspect both OLD and NEW transition relations to make sure to only fire after status was actually changed in the update. If you don't need this, remove the OLD relation.

Unfortunately, we cannot restrict to UPDATE OF status right away, as that is currently not implemented:

The standard allows transition tables to be used with column-specific UPDATE triggers, but then the set of rows that should be visible in the transition tables depends on the trigger's column list. This is not currently implemented by PostgreSQL.

I join OLD to NEW transition relation USING id. Creates a corner case where this can go wrong after updating table1.id. Typically, that never happens, but take precautions.

To avoid an update loop, I added WHEN (pg_trigger_depth() = 0) to the trigger. See:

• How to prevent a PostgreSQL trigger from being fired by another trigger?

The function only uses a single DDL statement with (data-modifying) CTEs. Should be most efficient. See:

• Insert data in 3 tables at a time using Postgres

If no qualifying rows are found, INSERT and UPDATE are skipped. Nothing happens.

I added a couple of tests to the fiddle to verify it works as advertised.