Updating Associated Tags for a given Thing in one query.

update.sql

WITH
thing_id(i) AS (VALUES (123)),
new_tag_list(t) as (
  VALUES ('t1', 't2')
),
inserted_tags(tagid, thingid) AS (
  INSERT INTO tags_things
    (tag_id, thing_id)
  SELECT nt.id,
    (SELECT id FROM thing_id LIMIT 1)
  FROM tags nt
  WHERE nt.name IN
    (SELECT t FROM new_tag_list) AND
  NOT EXISTS (
    SELECT 1 FROM tags_things
    WHERE
      tags_things.tag_id = nt.id
  )
  RETURNING tag_id, thing_id
),
deleted_tags(tagid, thingid) AS (
  DELETE FROM tags_things
  WHERE EXISTS (
    SELECT 1 FROM tags
    WHERE name NOT IN
      (SELECT t FROM new_tag_list)
  )
  RETURNING tag_id, thing_id
),
staying_tags AS (
  SELECT tt.tag_id, tt.thing_id
  FROM tags_things tt
  INNER JOIN (
    tags INNER JOIN new_tag_list ON
    tags.name = new_tag_list.t
   )
),
summary AS (
  SELECT 'added', * FROM inserted_tags UNION
  SELECT 'removed', * FROM deleted_tags UNION
  SELECT 'staying', * FROM staying_tags
)
SELECT summary.*, t.name
FROM summary INNER JOIN tags ON tags.id = summary.tag_id;

Related to conversation with "[email protected]" on Mastodon: https://toot.community/@chakie/111273651476417186

I wonder how efficient this query would be compared to a DELETE and a number of discrete INSERT:s.