SQL基础知识

查看插入结果
profiles 是表的名称

SELECT * FROM profiles;

-- ============================================
-- 0️⃣ 删除旧表（如果存在）
-- ============================================
DROP TABLE IF EXISTS public.profiles;

-- ============================================
-- 1️⃣ 创建 profiles 表
-- user_id 由触发器生成，不从外部传入
-- ============================================
CREATE TABLE public.profiles (
    id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    user_id text NOT NULL,
    username text,
    avatar_url text,
    bio text,
    created_at timestamp with time zone DEFAULT now(),
    CONSTRAINT unique_user_id UNIQUE (user_id)
);

-- ============================================
-- 2️⃣ 启用行级安全策略（RLS）
-- ============================================
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view their own profile"
ON public.profiles
FOR SELECT
USING (auth.uid() = user_id::uuid);  -- 如果 auth.uid() 返回 UUID，可根据需要调整

CREATE POLICY "Users can insert their own profile"
ON public.profiles
FOR INSERT
WITH CHECK (auth.uid() = user_id::uuid);

CREATE POLICY "Users can update their own profile"
ON public.profiles
FOR UPDATE
USING (auth.uid() = user_id::uuid);

-- ============================================
-- 3️⃣ 创建生成唯一 8 位字母数字 ID 的函数
-- ============================================
CREATE OR REPLACE FUNCTION generate_unique_userid()
RETURNS text AS $$
DECLARE
    new_id text;
BEGIN
    LOOP
        -- 生成随机 8 位 base62 字母数字组合
        new_id := substring(encode(gen_random_bytes(6), 'base64') FROM 1 FOR 8);
        -- 替换掉 + / =
        new_id := regexp_replace(new_id, '[+/=]', 'A', 'g');
        -- 检查是否唯一
        IF NOT EXISTS (SELECT 1 FROM public.profiles WHERE user_id = new_id) THEN
            RETURN new_id;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- 4️⃣ 创建触发器函数，在插入时自动生成 user_id
-- ============================================
CREATE OR REPLACE FUNCTION set_userid_before_insert()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.user_id IS NULL THEN
        NEW.user_id := generate_unique_userid();
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_generate_userid
BEFORE INSERT ON public.profiles
FOR EACH ROW
EXECUTE FUNCTION set_userid_before_insert();

-- ============================================
-- 5️⃣ 插入 5 条测试用户（不传 user_id）
-- ============================================
INSERT INTO public.profiles (username, avatar_url, bio)
VALUES 
('Alice', 'https://example.com/avatar1.png', '测试用户 Alice'),
('Bob', 'https://example.com/avatar2.png', '测试用户 Bob'),
('Charlie', 'https://example.com/avatar3.png', '测试用户 Charlie'),
('David', 'https://example.com/avatar4.png', '测试用户 David'),
('Eve', 'https://example.com/avatar5.png', '测试用户 Eve');

SELECT * FROM profiles;