Skip to content

Instantly share code, notes, and snippets.

@troyk
Created April 10, 2019 23:33
Show Gist options
  • Save troyk/772e2d72dfc15097caf89794bcfa6b7b to your computer and use it in GitHub Desktop.
Save troyk/772e2d72dfc15097caf89794bcfa6b7b to your computer and use it in GitHub Desktop.
Polymorphic relationships with triggers in postgresql
-- 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');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment