clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805464 fiddles created (40820 in the last week).

-- Configurations CREATE TABLE config ( id INT PRIMARY KEY, name VARCHAR(100), current_revision_id INT ); -- Have multiple revisions CREATE TABLE revision ( id INT PRIMARY KEY, config_id INT NOT NULL REFERENCES config(id), created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, description VARCHAR, foo INT NOT NULL, bar BOOLEAN NOT NULL, deployed BOOLEAN NOT NULL DEFAULT FALSE ); -- A configuration has one _current_ revision ALTER TABLE config ADD CONSTRAINT current_revision_id_fk FOREIGN KEY (current_revision_id) REFERENCES revision(id); -- Revisions are automatically numbered in a view CREATE VIEW numbered_revision AS ( SELECT *, row_number() OVER ( PARTITION BY config_id ORDER BY created_at, id ) AS number FROM revision ); -- Configurations have multiple 'public names' CREATE TABLE public_name ( id INT PRIMARY KEY, revision_id INT NOT NULL REFERENCES revision(id), name VARCHAR(100), UNIQUE (revision_id, name) ); -- Published public names must be unique across all configurations CREATE TABLE published_public_name ( id INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, config_id INT NOT NULL REFERENCES config(id) ON DELETE CASCADE, name VARCHAR(100) NOT NULL UNIQUE ); -- Published names are copied by a trigger, this enforces the -- constraint that such names must be globally unique CREATE OR REPLACE FUNCTION copy_published_public_names() RETURNS TRIGGER AS $BODY$ BEGIN -- OLD / NEW is a row in the config table -- Trigger is called for both updates and inserts IF TG_OP = 'UPDATE' THEN IF OLD.current_revision_id IS NOT DISTINCT FROM NEW.current_revision_id THEN -- Nothing changed RETURN NEW; END IF; IF OLD.current_revision_id IS NOT NULL THEN DELETE FROM published_public_name WHERE id IN ( SELECT ppn.id FROM published_public_name ppn WHERE ppn.config_id = OLD.id ORDER BY ppn.name ); END IF; END IF; IF NEW.current_revision_id IS NOT NULL THEN INSERT INTO published_public_name(config_id, name) SELECT NEW.id, pn.name FROM public_name pn WHERE pn.revision_id = NEW.current_revision_id ORDER BY pn.name; END IF; RETURN NEW; END $BODY$ LANGUAGE plpgsql; CREATE TRIGGER copy_published_public_names_trigger BEFORE INSERT OR UPDATE OF current_revision_id ON config FOR EACH ROW EXECUTE FUNCTION copy_published_public_names();
 hidden batch(es)


INSERT INTO config (id, name) VALUES (17, 'config_foo'), (42, 'config_bar'); INSERT INTO revision (id, config_id, created_at, description, foo, bar) VALUES (11, 17, '2021-05-29 09:07:18', 'Foo configuration, first draft', 81, TRUE), (19, 17, '2021-05-29 10:42:17', 'Foo configuration, second draft', 73, TRUE), (23, 42, '2021-05-29 09:36:52', 'Bar configuration, first draft', 118, FALSE); INSERT INTO public_name (id, revision_id, name) VALUES -- public names for foo configuration, first draft (83, 11, 'some.name'), (84, 11, 'other.name'), -- public names for foo configuration, second draft (85, 19, 'revised.name'), (86, 19, 'other.name'), (87, 19, 'third.name'), -- public names for bar configuration, first draft; -- some of the names here are the same used by foo configurations (88, 23, 'some.name'), (89, 23, 'unique.name'), (90, 23, 'other.name'); -- Foo configuration has a current, published revision: UPDATE config SET current_revision_id = 19 WHERE id = 17; UPDATE revision SET deployed = TRUE WHERE id in (11, 19);
2 rows affected
3 rows affected
8 rows affected
1 rows affected
2 rows affected
 hidden batch(es)


SELECT c.name AS config, rev.number AS revision, rev.deployed, CASE WHEN c.current_revision_id = rev.id THEN 'ACTIVE' ELSE '' END AS status, string_agg(p.name, ', ' ORDER BY p.name) AS names FROM config c JOIN numbered_revision AS rev ON c.id = rev.config_id JOIN public_name p ON p.revision_id = rev.id GROUP BY c.id, rev.id, rev.number , rev.deployed ORDER BY c.id, rev.number;
config revision deployed status names
config_foo 1 t other.name, some.name
config_foo 2 t ACTIVE other.name, revised.name, third.name
config_bar 1 f other.name, some.name, unique.name
 hidden batch(es)


-- attempt to make the first revision for bar configuration public -- This should _fail_ due to 'other.name' already being claimed by -- foo config. UPDATE config SET current_revision_id = 23 WHERE id = 42; UPDATE revision SET deployed = TRUE WHERE id = 23;
ERROR: duplicate key value violates unique constraint "published_public_name_name_key" DETAIL: Key (name)=(other.name) already exists. CONTEXT: SQL statement "INSERT INTO published_public_name(config_id, name) SELECT NEW.id, pn.name FROM public_name pn WHERE pn.revision_id = NEW.current_revision_id ORDER BY pn.name" PL/pgSQL function copy_published_public_names() line 23 at SQL statement
 hidden batch(es)


SELECT c.name AS config, rev.number AS revision, rev.deployed, CASE WHEN c.current_revision_id = rev.id THEN 'ACTIVE' ELSE '' END AS status, string_agg(p.name, ', ' ORDER BY p.name) AS names FROM config c JOIN numbered_revision AS rev ON c.id = rev.config_id JOIN public_name p ON p.revision_id = rev.id GROUP BY c.id, rev.id, rev.number , rev.deployed ORDER BY c.id, rev.number;
config revision deployed status names
config_foo 1 t other.name, some.name
config_foo 2 t ACTIVE other.name, revised.name, third.name
config_bar 1 f other.name, some.name, unique.name
 hidden batch(es)


-- if foo relinguises the name, bar can set their revision as current: UPDATE config SET current_revision_id = NULL WHERE id = 17; UPDATE config SET current_revision_id = 23 WHERE id = 42; UPDATE revision SET deployed = TRUE WHERE id = 23;
1 rows affected
1 rows affected
1 rows affected
 hidden batch(es)


SELECT c.name AS config, rev.number AS revision, rev.deployed, CASE WHEN c.current_revision_id = rev.id THEN 'ACTIVE' ELSE '' END AS status, string_agg(p.name, ', ' ORDER BY p.name) AS names FROM config c JOIN numbered_revision AS rev ON c.id = rev.config_id JOIN public_name p ON p.revision_id = rev.id GROUP BY c.id, rev.id, rev.number , rev.deployed ORDER BY c.id, rev.number;
config revision deployed status names
config_foo 1 t other.name, some.name
config_foo 2 t other.name, revised.name, third.name
config_bar 1 t ACTIVE other.name, some.name, unique.name
 hidden batch(es)