给定3个表的postgres数据库:
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)
userlinks表作为一个交叉引用存在,将url段关联到一个或多个用户。
当一个用户创建了一个新的slug_id,我想插入到url表中,使用在那里创建的slug_id,插入到userlinks与当前用户ID和slug_id
如果可能的话,将两个结果作为记录表返回。当前用户id可以通过auth.uid()访问我使用supabase
中的存储过程来完成此操作我已经走了这么远,但我卡住了:
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)
试试这个:
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