53 lines
1.5 KiB
PL/PgSQL
53 lines
1.5 KiB
PL/PgSQL
-- 建表
|
||
create table public.user_configs (
|
||
id bigint generated by default as identity not null,
|
||
created_at timestamp with time zone not null default now(),
|
||
data json null,
|
||
updated_at text null,
|
||
user_id uuid not null,
|
||
constraint user_configs_pkey primary key (id),
|
||
constraint user_configs_user_id_key unique (user_id)
|
||
) TABLESPACE pg_default;
|
||
|
||
-- 启用行级安全（RLS）
|
||
alter table public.user_configs enable row level security;
|
||
|
||
drop policy if exists "user_configs_select_own" on public.user_configs;
|
||
drop policy if exists "user_configs_insert_own" on public.user_configs;
|
||
drop policy if exists "user_configs_update_own" on public.user_configs;
|
||
|
||
create policy "user_configs_select_own"
|
||
on public.user_configs
|
||
for select
|
||
using (auth.uid() = user_id);
|
||
|
||
create policy "user_configs_insert_own"
|
||
on public.user_configs
|
||
for insert
|
||
with check (auth.uid() = user_id);
|
||
|
||
create policy "user_configs_update_own"
|
||
on public.user_configs
|
||
for update
|
||
using (auth.uid() = user_id)
|
||
with check (auth.uid() = user_id);
|
||
|
||
-- 增量更新函数
|
||
create or replace function public.update_user_config_partial(payload jsonb)
|
||
returns void
|
||
language plpgsql
|
||
security definer
|
||
set search_path = public
|
||
as
|
||
$$
|
||
begin
|
||
update public.user_configs
|
||
set data = ((coalesce(data::jsonb, '{}'::jsonb) || payload)::json),
|
||
updated_at = now()
|
||
where user_id = auth.uid();
|
||
end;
|
||
$$;
|
||
|
||
grant execute on function public.update_user_config_partial(jsonb) to authenticated;
|
||
grant execute on function public.update_user_config_partial(jsonb) to service_role;
|