Postgres 15

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$;

CREATE TABLE



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