postgres insert into multiple tables after each other and return everything

Question

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)

Edouard · Accepted Answer · 2021-10-22 10:16:16Z

1

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

answered Oct 22, 2021 at 10:16

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres insert into multiple tables after each other and return everything

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related