postgres insert into multiple tables after each other and return everything-CodePudding

Given postgres database with 3 tables:

users(user_id: uuid, ...)
urls(slug_id:int8 pkey, slug:text unique not null, long_url:text not null)
userlinks(user_id:fkey users.user_id, slug_id:fkey urls.slug_id)
          pkey(user_id, slug_id)

The userlinks table exists as a cross reference to associate url slugs to one or more users.

When a new slug is created by a user I'd like to INSERT into the urls table, take the slug_id that was created there, INSERT into userlinks with current users ID and slug_id

Then if possible return both results as a table of records. Current users id is accessible with auth.uid() I'm doing this with a stored procedure in supabase

I've gotten this far but I'm stuck:

WITH urls_row as (
  INSERT INTO urls(slug, long_url)
  VALUES ('testslug2', 'testlong_url2')
  RETURNING slug_id
)
INSERT INTO userlinks(user_id, slug_id)
VALUES (auth.uid(), urls_row.slug_id)
--RETURNING *

--RETURNING (urls_record, userlinks_record)

CodePudding user response：

Try this :

WITH urls_row as (
  INSERT INTO urls(slug, long_url)
  VALUES ('testslug2', 'testlong_url2')
  RETURNING slug_id
), userlink_row AS (
  INSERT INTO userlinks(user_id, slug_id)
  SELECT auth.uid(), urls_row.slug_id
  FROM urls_row
  RETURNING *
)
SELECT *
FROM urls_row AS ur
INNER JOIN userlink_row AS us
ON ur.slug_id = us.slug_id