如何在Supabase数据库函数中实现邮箱用户查询或创建并返回ID？

以下是满足需求的完整函数代码，在原有查询逻辑基础上添加了用户创建分支：

CREATE OR REPLACE FUNCTION public.get_or_create_user_by_email(email text)
RETURNS uuid AS $$
DECLARE
    user_id uuid;
BEGIN
    -- 查询匹配邮箱的现有用户ID
    SELECT id INTO user_id FROM auth.users WHERE auth.users.email = $1;
    
    -- 若用户不存在，创建新用户并获取其ID
    IF user_id IS NULL THEN
        -- 根据auth.users表结构调整必填字段，示例包含常见字段
        INSERT INTO auth.users (email, email_confirmed_at, password)
        VALUES ($1, NOW(), '') -- password字段按需设置，无密码需求可留空或用默认值
        RETURNING id INTO user_id;
    END IF;
    
    RETURN user_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

逻辑说明 #

1. 优先查询auth.users表中对应邮箱的用户ID，存入变量user_id。
2. 若查询结果为空（用户不存在），执行INSERT语句创建新用户，通过RETURNING id直接获取新生成的用户ID并赋值给user_id。
3. 最终返回用户ID，无论该用户是已存在还是新创建的。

注意事项 #

• 表字段适配：auth.users表可能存在其他必填字段（如role、encrypted_password等），需根据实际表结构修改INSERT语句中的字段列表和对应值。
• Supabase场景优化：如果是基于Supabase的auth.users表，建议使用官方提供的auth.sign_up()函数创建用户，避免直接操作表引发的权限或数据一致性问题，示例代码如下：

IF user_id IS NULL THEN
    SELECT id INTO user_id FROM auth.sign_up(
        json_build_object(
            'email', $1,
            'password', '', -- 无密码需求可设为空，或生成随机密码
            'email_confirm', true
        )
    );
END IF;

• 安全权限控制：由于使用了SECURITY DEFINER，需确保函数所有者拥有操作auth.users表的权限，同时限制函数的执行范围，避免滥用：

REVOKE ALL ON FUNCTION public.get_or_create_user_by_email(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.get_or_create_user_by_email(text) TO your_allowed_role;

内容的提问来源于stack exchange，提问作者Igor Grechishkin