foreign keys trigger.sql

CREATE OR REPLACE FUNCTION check_fk_child() RETURNS trigger AS $$
DECLARE
  fk_local TEXT := TG_ARGV[0];
  parent_table TEXT := TG_ARGV[1];
  fk_val INT;
  is_valid BOOLEAN;
  query TEXT;
BEGIN
  -- fk_val = getattr(NEW, fk_local)
  EXECUTE format('SELECT $1.%I', fk_local) USING NEW INTO fk_val;
  query := format('SELECT id IS NOT NULL FROM %I WHERE id = %L FOR SHARE', parent_table, fk_val);
  EXECUTE query INTO is_valid;
  RAISE DEBUG '%, %', query, is_valid;
  IF is_valid IS NULL THEN
    RAISE EXCEPTION 'insert or update on table "%" violates foreign key trigger "%"', TG_RELNAME, TG_NAME USING 
      DETAIL=format('Key (%s)=(%s) is not present in table "%s".', fk_local, fk_val, parent_table);
  END IF;
  return NEW;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION check_fk_parent() RETURNS trigger AS $$
DECLARE
  child_table TEXT := TG_ARGV[0];
  fk_name TEXT := TG_ARGV[1];
  is_referenced BOOLEAN;
  query TEXT;
BEGIN
  IF TG_OP = 'DELETE' OR NEW.id != OLD.id THEN
    query := format('SELECT %I IS NOT NULL FROM %I WHERE %I = %L LIMIT 1', fk_name, child_table, fk_name, OLD.id);
    EXECUTE query INTO is_referenced;
    RAISE DEBUG '%, %', query, is_referenced;
    IF is_referenced IS NOT NULL THEN
      RAISE EXCEPTION 'update or delete on table "%" violates foreign key trigger "%" from table "%"', TG_RELNAME, TG_NAME, child_table USING
        DETAIL=format('Key (id)=(%s) is still referenced from table "%s".', OLD.id, child_table);
    END IF;
  END IF;
  IF TG_OP = 'DELETE' THEN
    RETURN OLD;
  ELSE
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE 'plpgsql';


CREATE TABLE parent (
  id SERIAL PRIMARY KEY
);

CREATE TABLE child (
  parent_id INT -- REFERENCES parent(id)
);

CREATE TRIGGER fk AFTER DELETE OR UPDATE OF id ON parent FOR EACH ROW EXECUTE PROCEDURE check_fk_parent('child', 'parent_id');
CREATE TRIGGER fk AFTER INSERT OR UPDATE OF parent_id ON child FOR EACH ROW EXECUTE PROCEDURE check_fk_child('parent_id', 'parent');

INSERT INTO child VALUES (1);
-- ERROR:  insert or update on table "child" violates foreign key trigger "fk"
-- DETAIL:  Key (parent_id)=(1) is not present in table "parent".
INSERT INTO parent VALUES (1);
INSERT INTO child VALUES (1);
DELETE FROM parent;
-- ERROR:  update or delete on table "parent" violates foreign key trigger "fk" from table "child"
-- DETAIL:  Key (id)=(1) is still referenced from table "child".