Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- cleaned 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()
);

-- dummy rows
INSERT INTO table1 (id, jobs_ids, status) VALUES
  (1, '{}', 2)
, (2, '{}', 2)
, (3, '{}', 2)
, (4, '{}', 2)
, (5, '{}', 2)
, (6, '{}', 2)
;

CREATE TABLE



CREATE TABLE



INSERT 0 6


-- DROP FUNCTION job_id_trigger_table1();
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$;

-- DROP TRIGGER job_id_trigger_table1 ON table1;
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();

CREATE FUNCTION



CREATE TRIGGER


UPDATE table1 SET status = 3 WHERE id = 1;  -- single row
UPDATE table1 SET status = 1 WHERE id = 2;  -- no qualifying rows
UPDATE table1 SET status = 3 WHERE id IN (5,6);  -- multiple rows
UPDATE table1 SET status = 3 WHERE id IN (5,6);  -- no qualifying rows: jobs_ids = '{}' IS NOT TRUE
UPDATE table1 SET status = 3 WHERE id IN (2,3,4,5);  -- 5 does not qualify: jobs_ids = '{}' IS NOT TRUE

UPDATE 1



UPDATE 1



UPDATE 2



UPDATE 2



UPDATE 4


TABLE table1;
TABLE job;


id
jobs_ids
status


1
{1}
3

6
{2}
3

5
{2}
3

2
{3}
3

3
{3}
3

4
{3}
3



SELECT 6




id
type_id
description
created_at
modified_at


1
1
automated jobid for IDs {1}
2022-10-06 04:55:31.303842+01
2022-10-06 04:55:31.303842+01

2
1
automated jobid for IDs {5,6}
2022-10-06 04:55:31.303842+01
2022-10-06 04:55:31.303842+01

3
1
automated jobid for IDs {2,3,4}
2022-10-06 04:55:31.303842+01
2022-10-06 04:55:31.303842+01



SELECT 3

id	type_id	description	created_at	modified_at
1	1	automated jobid for IDs {1}	2022-10-06 04:55:31.303842+01	2022-10-06 04:55:31.303842+01
2	1	automated jobid for IDs {5,6}	2022-10-06 04:55:31.303842+01	2022-10-06 04:55:31.303842+01
3	1	automated jobid for IDs {2,3,4}	2022-10-06 04:55:31.303842+01	2022-10-06 04:55:31.303842+01