Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE public.tag (
  tag_id   int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
, tag_slug text UNIQUE NOT NULL
, tag_name text NOT NULL
);

-- 1. Taking 2 separate arrays:
CREATE OR REPLACE FUNCTION public.f_upsert_tags1(_tag_slugs text[], _tag_names text[])
  RETURNS SETOF public.tag
  LANGUAGE sql AS
$func$
INSERT INTO public.tag AS t (tag_slug, tag_name) 
SELECT *
FROM   unnest($1, $2)
ON     CONFLICT (tag_slug) DO UPDATE
SET    tag_name = EXCLUDED.tag_name
RETURNING *
$func$;

-- 2. Taking a JSON array of objects
CREATE OR REPLACE FUNCTION public.f_upsert_tags2(_tags json)
  RETURNS SETOF public.tag
  LANGUAGE sql AS
$func$
INSERT INTO public.tag AS t (tag_slug, tag_name) 
SELECT i.tag_slug, i.tag_name
FROM   json_populate_recordset(null::public.tag, $1) i
ON     CONFLICT (tag_slug) DO UPDATE
SET    tag_name = EXCLUDED.tag_name
RETURNING *
$func$;


-- 3. Optimized version, based on Jon's version
/* 
  - take JSON array
  - avoid empty updates
  - still return *all* input rows, even under concurrent write load
  - only return tag_slug, tag_id

-- Makes sense for
  - "many conflicts but few actual updates"
  - concurrent writes can actually compete for same rows
  - "return value only needs tag_id ... subsequent write to through table to record a many:many relationship
    (I added tag_slug to allow link back.)
*/
CREATE FUNCTION upsert_tags(_tags json)
   RETURNS TABLE (tag_slug text, tag_id text)
   LANGUAGE sql AS
$func$
INSERT INTO tag AS t (tag_slug, tag_name)
SELECT i.tag_slug, i.tag_name
FROM   json_populate_recordset(null::public.tag, _tags) i
ON     CONFLICT (tag_slug) DO UPDATE
SET    tag_name = EXCLUDED.tag_name
WHERE  t.tag_name <> EXCLUDED.tag_name;  -- omit empty updates

-- New statement to include previously invisible, concurrently written rows
SELECT t.tag_slug, t.tag_id
FROM   json_populate_recordset(NULL::public.tag, _tags)
JOIN   public.tag t USING (tag_slug);  -- JOIN beats IN without need for removing dupes
$func$;

CREATE TABLE



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION


-- arrays
SELECT *
FROM   public.f_upsert_tags1('{first-tag, second-tag}'
                           , '{First Tag, Second Tag}');


tag_id
tag_slug
tag_name


1
first-tag
First Tag

2
second-tag
Second Tag



SELECT 2


-- json
SELECT *
FROM   public.f_upsert_tags2('[{"tag_slug":"first-tag", "tag_name":"First Tag2"}
                             , {"tag_slug":"second-tag", "tag_name":"Second Tag2"}]');


tag_id
tag_slug
tag_name


1
first-tag
First Tag2

2
second-tag
Second Tag2



SELECT 2


-- optimized
SELECT *
FROM   public.upsert_tags('[{"tag_slug":"first-tag", "tag_name":"First Tag"}
                          , {"tag_slug":"second-tag", "tag_name":"Second Tag2"}
                          , {"tag_slug":"third-tag", "tag_name":"Third Tag"}]');

TABLE public.tag;


tag_slug
tag_id


second-tag
2

first-tag
1

third-tag
7



SELECT 3




tag_id
tag_slug
tag_name


2
second-tag
Second Tag2

1
first-tag
First Tag

7
third-tag
Third Tag



SELECT 3
tag_id	tag_slug	tag_name
2	second-tag	Second Tag2
1	first-tag	First Tag
7	third-tag	Third Tag