add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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
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