能否用PL/pgSQL实现用户表PartnerID的随机配对逻辑？

可行性说明 #

完全可行。Supabase原生支持PL/pgSQL自定义函数，且默认启用pg_cron扩展用于定时任务，你的临时表处理思路逻辑清晰，能有效实现需求中的配对规则。

核心实现思路 #

• 先处理Flag = false的用户，直接将其partnerID设为NULL
• 筛选出Flag = true的符合条件用户，重置其旧配对关系
• 通过临时表对符合条件用户做随机排序，利用行号实现两两唯一配对
• 双向更新配对双方的partnerID，确保配对关系互斥且双向绑定
• 处理奇数数量的情况：最后一个未配对的用户partnerID保持NULL
• 清理临时表，将结果同步至原USER表

PL/pgSQL函数代码 #

CREATE OR REPLACE FUNCTION update_partner_pairs()
RETURNS VOID AS $$
DECLARE
    eligible_count INT;
BEGIN
    -- 规则1：Flag为false的用户，partnerID强制设为NULL
    UPDATE "USER"
    SET "partnerID" = NULL
    WHERE "Flag" = false;

    -- 重置所有符合条件用户的旧配对
    UPDATE "USER"
    SET "partnerID" = NULL
    WHERE "Flag" = true;

    -- 获取符合条件的用户总数
    SELECT COUNT(*) INTO eligible_count
    FROM "USER"
    WHERE "Flag" = true;

    -- 若符合条件用户不足2个，无需配对直接返回
    IF eligible_count < 2 THEN
        RETURN;
    END IF;

    -- 创建临时表，存储随机排序后的符合条件用户及行号
    CREATE TEMP TABLE temp_eligible_users AS
    SELECT "ID",
           ROW_NUMBER() OVER (ORDER BY random()) AS rn
    FROM "USER"
    WHERE "Flag" = true;

    -- 规则2、3：双向配对奇数行与下一个偶数行的用户
    -- 给奇数行用户设置partnerID为对应偶数行用户的ID
    UPDATE "USER" u1
    SET "partnerID" = u2."ID"
    FROM temp_eligible_users te1
    JOIN temp_eligible_users te2 ON te1.rn + 1 = te2.rn AND te1.rn % 2 = 1
    WHERE u1."ID" = te1."ID";

    -- 给偶数行用户设置partnerID为对应奇数行用户的ID
    UPDATE "USER" u2
    SET "partnerID" = u1."ID"
    FROM temp_eligible_users te1
    JOIN temp_eligible_users te2 ON te1.rn + 1 = te2.rn AND te1.rn % 2 = 1
    WHERE u2."ID" = te2."ID";

    -- 清理临时表
    DROP TABLE temp_eligible_users;
END;
$$ LANGUAGE plpgsql;

设置Supabase CRON定时任务 #

执行以下SQL创建定时任务（示例为每天凌晨2点运行一次，可根据需求调整CRON表达式）：

-- 创建每日配对任务
SELECT cron.schedule(
    'daily-partner-pairing', -- 任务名称
    '0 2 * * *', -- CRON表达式：每天2点整执行
    'SELECT update_partner_pairs();' -- 要执行的函数
);

规则验证 #

1. Flag为false的用户：函数开头直接将这类用户的partnerID设为NULL，完全符合规则1
2. partnerID唯一性：通过临时表的行号两两配对，每个用户仅被分配一次，不会出现重复分配，符合规则2
3. 双向配对：两次UPDATE分别设置配对双方的partnerID，确保A的partner是B时，B的partner必然是A，符合规则3
4. 奇数数量处理：当符合条件用户数为奇数时，最后一个用户的行号是奇数，不会进入配对逻辑，partnerID保持NULL，符合规则4

内容的提问来源于stack exchange，提问作者D.Doe