clear markdown compare help donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
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. 462178 distinct fiddles created so far.

CREATE TABLE tag ( tag_id serial , tag text NOT NULL ); CREATE TABLE task ( task_id serial , task text NOT NULL ); CREATE TABLE task_tag ( task_id int , tag_id int ); INSERT INTO tag (tag) SELECT 'tag' || g FROM generate_series(1,5000) g; INSERT INTO task (task) SELECT 'task' || g FROM generate_series(1,5000) g; INSERT INTO task_tag (task_id, tag_id) SELECT task_id, tag_id FROM task ts, LATERAL (SELECT tag_id FROM tag WHERE random() > 0.996) tg; ALTER TABLE task ADD PRIMARY KEY (task_id); ALTER TABLE tag ADD PRIMARY KEY (tag_id); ALTER TABLE task_tag ADD PRIMARY KEY (tag_id, task_id) -- columns in this order , ADD CONSTRAINT task_task_id_fkey FOREIGN KEY (task_id) REFERENCES task (task_id) , ADD CONSTRAINT tag_tag_id_fkey FOREIGN KEY (tag_id) REFERENCES tag (tag_id); INSERT INTO task_tag (task_id, tag_id) VALUES (123, 123), (123, 456) , (321, 123), (321, 456) ON CONFLICT DO NOTHING; -- make sure we have two hits CREATE INDEX tag_covering_idx ON tag (tag_id, tag); ANALYZE tag; ANALYZE task; ANALYZE task_tag;
5000 rows affected
5000 rows affected
120000 rows affected
4 rows affected
 hidden batch(es)


CREATE OR REPLACE FUNCTION f_tasks_with_tags(VARIADIC _tags text[]) RETURNS SETOF task AS $func$ WITH RECURSIVE cte AS ( SELECT task_id, 1 AS idx FROM task_tag WHERE tag_id = (SELECT tag_id FROM tag WHERE tag = _tags[1]) UNION SELECT task_id, c.idx + 1 FROM cte c JOIN task_tag tt USING (task_id) WHERE tag_id = (SELECT tag_id FROM tag WHERE tag = _tags[c.idx + 1]) ) SELECT t.* FROM cte c JOIN task t USING (task_id) WHERE c.idx = array_length(_tags, 1) $func$ LANGUAGE sql STABLE;
 hidden batch(es)


SELECT * FROM f_tasks_with_tags('tag123', 'tag456');
task_id task
321 task321
123 task123
 hidden batch(es)