BEGIN;

CREATE TABLE things (
	pk BIGSERIAL PRIMARY KEY,
	some_data TEXT NOT NULL,
	standalone BOOLEAN NOT NULL
);

CREATE FUNCTION check_derived_is_child(BIGINT) RETURNS BOOLEAN AS $$
	SELECT EXISTS (
		SELECT 1 FROM things
		    WHERE pk=$1 AND standalone=TRUE
	);
$$ LANGUAGE sql;

CREATE FUNCTION check_derived_is_parent(BIGINT) RETURNS BOOLEAN AS $$
	SELECT EXISTS (
		SELECT 1 FROM things
		    WHERE pk=$1 AND standalone=FALSE
	);
$$ LANGUAGE sql;

CREATE TABLE derived_things (
	parent BIGINT NOT NULL REFERENCES things(pk),
	child BIGINT NOT NULL REFERENCES things(pk),
	arbitrary_data TEXT NOT NULL,
	CONSTRAINT derived_things_check_child CHECK (check_derived_is_child(child)),
	CONSTRAINT derived_things_check_parent CHECK (check_derived_is_parent(parent)),
	PRIMARY KEY (parent, child)
);

ALTER TABLE derived_things DROP CONSTRAINT IF EXISTS derived_things_check_child;
ALTER TABLE derived_things DROP CONSTRAINT IF EXISTS derived_things_check_parent;
DROP FUNCTION IF EXISTS check_derived_is_child(BIGINT);
DROP FUNCTION IF EXISTS check_derived_is_parent(BIGINT);

CREATE OR REPLACE FUNCTION derived_things_family_check() RETURNS TRIGGER AS $$
BEGIN
	IF NOT EXISTS (SELECT 1 FROM things WHERE pk=NEW.child AND
	    standalone=TRUE) THEN
		RAISE EXCEPTION 'Ein Ding kann nicht aus abgeleiteten Dingern abgeleitet werden!'
		    USING HINT = 'Wunschding parent(' || NEW.parent ||
		    ') child(' || NEW.child || ') data(' || NEW.arbitrary_data || ')';
	END IF;
	IF NOT EXISTS (SELECT 1 FROM things WHERE pk=NEW.parent AND
	    standalone=FALSE) THEN
		RAISE EXCEPTION 'Ein Basisding kann keine Ableitungsquellen haben!'
		    USING HINT = 'Wunschding parent(' || NEW.parent ||
		    ') child(' || NEW.child || ') data(' || NEW.arbitrary_data || ')';
	END IF;
	RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER derived_things_family_check
    BEFORE INSERT OR UPDATE OF parent, child ON derived_things
    FOR EACH ROW EXECUTE PROCEDURE derived_things_family_check();

-- these will succeed
INSERT INTO things VALUES (1, 'foo', TRUE);
INSERT INTO things VALUES (2, 'bar', TRUE);
INSERT INTO things VALUES (3, 'foobar', FALSE);
INSERT INTO things VALUES (4, 'foofoobar', FALSE);
INSERT INTO things VALUES (5, 'baz', TRUE);
INSERT INTO derived_things VALUES (3, 1, 'foobar on foo');
INSERT INTO derived_things VALUES (3, 2, 'foobar on bar');
INSERT INTO derived_things VALUES (4, 1, 'foofoobar on foo');

COMMIT;

-- these will not succeed due to the check constraints
INSERT INTO derived_things VALUES (4, 3, 'foofoobar on foobar');
INSERT INTO derived_things VALUES (5, 1, 'baz on foo');

-- show
SELECT * FROM derived_things;
SELECT * FROM things;
