0

I have a PostgreSQL 15 join table schema.m2mjointable to link records of several parent tables (schema.table_a, schema.table_b, ...) with other records of those same parent tables:

id ref_table ref_id rep_table rep_id
IDENTITY TEXT INT TEXT INT
---------- -------------- -------- -------------- --------
1 table_a 1 table_a 2
2 table_a 1 table_c 3
3 table_a 4 table_b 7
4 table_b 5 table_a 8
5 table_b 9 table_b 11
6 table_c 7 table_b 11

In the parent tables (schema.table_a, schema.table_b, ...), there is always a boolean field named referenced which should tell if the record i of table n is at least referenced once by the pair of columns ref_table and ref_id in this join table. If that's the case it should be set to TRUE. It should be set to FALSE if it's never present in this pair of columns.

How could I build a trigger function to update the referenced field of the parent tables with such trigger:

CREATE TRIGGER schema.set_referenced_state_biudt
  BEFORE INSERT OR UPDATE OR DELETE
  ON schema.m2mjointable
  FOR EACH ROW EXECUTE PROCEDURE schema.set_referenced_state();
BEGIN;
CREATE OR REPLACE FUNCTION schema.set_referenced_state()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
   -- set the BOOL value of the "referenced" field to TRUE or FALSE
   -- in the record id=value_of(ref_id) of the parent table=value_of(ref_table) 
   -- corresponding to those values of the record being currently inserted/updated/deleted
END;
$$;

1 Answer 1

0

Here is what I currently achieved, it's working, but probably not perfect so feel free to improve this idea if you think there are drawbacks:

CREATE OR REPLACE FUNCTION schema.set_referenced_state()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
  IF (TG_OP = 'DELETE') THEN
    EXECUTE format('UPDATE %I.%I SET "referenced" = FALSE WHERE id = $1', TG_TABLE_SCHEMA, OLD.ref_table) USING OLD.ref_id;
    RETURN OLD;
  ELSIF (TG_OP = 'UPDATE' OR TG_OP = 'INSERT') THEN
    EXECUTE format('UPDATE %I.%I SET "referenced" = TRUE WHERE id = $1', TG_TABLE_SCHEMA, NEW.ref_table) USING NEW.ref_id;
    RETURN NEW;
  END IF;
END;
$$;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.