Polymorphic relationships with triggers in postgresql

just_use_fkeys.sql

-- saving here for later reference incase I decide to complicate things, decided to just use foreign keys

create table user_groups {
  id bigint PRIMARY KEY default id_generator(),
  account_id bigint not null references accounts(id) on delete cascade on update cascade,
  updated_at timestamptz,
  name citext not null
}

CREATE TYPE notification_level AS ENUM ('change', 'complete', 'digest', 'all');

create table members (
  --user_id bigint references users(id) on delete cascade on update cascade,
  --notifications notification_level,
  --permissions text[],
  --background_check_id bigint references background_checks(id) on delete cascade on update cascade,
  relid bigint not null,
  reltype regclass not null  
);

CREATE OR REPLACE FUNCTION reltype_fkey_references_trig() RETURNS "trigger" AS
$$
DECLARE
   ref_exists bool := null;
BEGIN
  IF TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND (OLD.relid IS DISTINCT FROM NEW.relid OR OLD.reltype IS DISTINCT FROM NEW.reltype)) THEN
    EXECUTE 'SELECT true FROM ' || NEW.reltype || ' WHERE id = $1' INTO ref_exists USING NEW.relid;
    IF ref_exists IS NULL THEN
      RAISE foreign_key_violation;
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER members_reltype_fkey_references_trig BEFORE INSERT OR UPDATE ON members FOR EACH ROW EXECUTE PROCEDURE reltype_fkey_references_trig();

CREATE OR REPLACE FUNCTION reltype_constraint_trig() RETURNS "trigger" AS
$$
BEGIN
  IF TG_OP = 'DELETE' THEN
    EXECUTE 'DELETE FROM ' || TG_ARGV[0]::regclass || ' WHERE relid = $1 AND reltype = $2::regclass' USING OLD.id,TG_TABLE_NAME;
    RETURN OLD;
  END IF;

  IF TG_OP = 'UPDATE' AND OLD.id  IS DISTINCT FROM NEW.id THEN
    EXECUTE 'UPDATE ' || TG_ARGV[0]::regclass || ' SET relid=$3 WHERE relid = $1 AND reltype = $2::regclass' USING OLD.id,TG_TABLE_NAME,NEW.id;    
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER members_reltype_constraint_trig AFTER DELETE OR UPDATE ON address_histories FOR EACH ROW EXECUTE PROCEDURE reltype_constraint_trig('members');
CREATE TRIGGER members_reltype_constraint_trig AFTER DELETE OR UPDATE ON background_checks FOR EACH ROW EXECUTE PROCEDURE reltype_constraint_trig('members');