Supabase下Postgres依次插入多表并返回全部结果的实现问题

你原有代码存在两处问题：

• 第二个INSERT引用CTE的urls_row.slug_id方式错误，不能直接在VALUES子句引用CTE结果，需要通过SELECT从CTE取数
• 要同时返回两张表的插入结果，需要新增第二个CTE存储userlinks的插入返回结果，最后关联两个CTE查询输出所有需要的字段

完整可运行SQL #

WITH urls_row AS (
  INSERT INTO urls(slug, long_url)
  VALUES ('testslug2', 'testlong_url2')
  -- 这里可按需调整要返回的urls表字段
  RETURNING slug_id, slug, long_url
),
userlinks_row AS (
  INSERT INTO userlinks(user_id, slug_id)
  SELECT auth.uid(), slug_id FROM urls_row
  -- 这里可按需调整要返回的userlinks表字段
  RETURNING user_id, slug_id
)
-- 关联两个CTE返回结果，需要全量字段可直接写SELECT u.*, ul.*
SELECT 
  u.slug_id, u.slug, u.long_url, ul.user_id
FROM urls_row u
JOIN userlinks_row ul ON u.slug_id = ul.slug_id;

封装为Supabase存储过程示例 #

如果需要封装为可重复调用的存储过程，可以用以下实现：

CREATE OR REPLACE FUNCTION create_user_slug(p_slug text, p_long_url text)
RETURNS TABLE (
  slug_id int8,
  slug text,
  long_url text,
  user_id uuid
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  RETURN QUERY
  WITH urls_row AS (
    INSERT INTO urls(slug, long_url)
    VALUES (p_slug, p_long_url)
    RETURNING slug_id, slug, long_url
  ),
  userlinks_row AS (
    INSERT INTO userlinks(user_id, slug_id)
    SELECT auth.uid(), slug_id FROM urls_row
    RETURNING user_id, slug_id
  )
  SELECT 
    u.slug_id, u.slug, u.long_url, ul.user_id
  FROM urls_row u
  JOIN userlinks_row ul ON u.slug_id = ul.slug_id;
END;
$$;

调用方式：SELECT * FROM create_user_slug('自定义slug', '目标长链接地址')即可直接拿到两张表的插入结果。

内容的提问来源于stack exchange，提问作者Szilvási Dávid