PostgreSQL -- JOIN UNNEST output with CTE INSERT ID -- INSERT many to many

Question

In a PostgreSQL function, is it possible to join the result of UNNEST, which is an integer array from function input, with an ID returned from a CTE INSERT?

I have PostgreSQL tables like:

CREATE TABLE public.message (
    id SERIAL PRIMARY KEY,
    content TEXT
);

CREATE TABLE public.message_tag (
    id SERIAL PRIMARY KEY,
    message_id INTEGER NOT NULL CONSTRAINT message_tag_message_id_fkey REFERENCES public.message(id) ON DELETE CASCADE,
    tag_id INTEGER NOT NULL CONSTRAINT message_tag_tag_id_fkey REFERENCES public.tag(id) ON DELETE CASCADE
);

I want to create a PostgreSQL function which takes input of content and an array of tag_id. This is for graphile. I want to do it all in one function, so I get a mutation.

Here's what I got so far. I don't know how to join an UNNEST across an id returned from a CTE.

CREATE FUNCTION public.create_message(content text, tags Int[])
RETURNS public.message
AS $$

-- insert to get primary key of message, for many to many message_id
WITH moved_rows AS (
  INSERT INTO public.message (content)
  RETURNING *;
)

-- many to many relation
INSERT INTO public.message_tag
SELECT moved_rows.id as message_id, tagInput.tag_id  FROM moved_rows, UNNEST(tags) as tagInput;
RETURNING *

$$ LANGUAGE sql VOLATILE STRICT;

score 1 · Accepted Answer · answered Jun 25 '21 at 22:47

You're not that far from your goal:

the semicolon placement in the CTE is wrong
the first INSERT statement lacks a SELECT or VALUES clause to specify what should be inserted
the INSERT into tag_message should specify the columns in which to insert (especially if you have that unnecessary serial id)
you specified a relation alias for the UNNEST call already, but none for the column tag_id
your function was RETURNING a set of message_tag rows but was specified to return a single message row

To fix these:

CREATE FUNCTION public.create_message(content text, tags Int[])
RETURNS public.message
AS $$

-- insert to get primary key of message, for many to many message_id
WITH moved_rows AS (
  INSERT INTO public.message (content)
  VALUES ($1)
  RETURNING *
),
-- many to many relation
_ AS (
INSERT INTO public.message_tag (message_id, tag_id)
SELECT moved_rows.id, tagInput.tag_id
FROM moved_rows, UNNEST($2) as tagInput(tag_id)
)
TABLE moved_rows;

$$ LANGUAGE sql VOLATILE STRICT;

_{(Online demo)}

Is it possible to return the message_tags too? https://dbfiddle.uk/?rdbms=postgres_13&fiddle=24875171bd392cecf2acf39c2bfdb981 I added RETURNS setof per graphile docs https://www.graphile.org/postgraphile/relations/#many-to-many-relations Thank you so much. — steve76, Jun 26 '21 at 03:39
got it! https://dbfiddle.uk/?rdbms=postgres_13&fiddle=8976eaef04d615994b7b9d14b1fa3607 — steve76, Jun 26 '21 at 03:54
I thought you were going to do `mutation { createMessage(…) { id text tags { name } } }` anyway, so returning the created message is more important — Bergi, Jun 26 '21 at 10:38

PostgreSQL -- JOIN UNNEST output with CTE INSERT ID -- INSERT many to many

1 Answers1