48 lines
No EOL
1.9 KiB
PL/PgSQL
48 lines
No EOL
1.9 KiB
PL/PgSQL
-- Migration pour créer la table professions_feminisations
|
|
|
|
-- Créer la table professions_feminisations
|
|
create table if not exists public.professions_feminisations (
|
|
id uuid primary key default gen_random_uuid(),
|
|
profession_code varchar not null unique,
|
|
profession_label varchar not null,
|
|
profession_feminine varchar not null,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
-- Créer un index sur le code profession pour des recherches rapides
|
|
create index if not exists idx_professions_feminisations_code
|
|
on public.professions_feminisations(profession_code);
|
|
|
|
-- Activer RLS sur la table
|
|
alter table public.professions_feminisations enable row level security;
|
|
|
|
-- Policy de lecture : accessible à tous les utilisateurs authentifiés
|
|
drop policy if exists professions_feminisations_read on public.professions_feminisations;
|
|
create policy professions_feminisations_read on public.professions_feminisations
|
|
for select using (auth.uid() is not null);
|
|
|
|
-- Policy d'écriture : seulement pour le staff
|
|
drop policy if exists professions_feminisations_staff_write on public.professions_feminisations;
|
|
create policy professions_feminisations_staff_write on public.professions_feminisations
|
|
for all using (
|
|
exists (
|
|
select 1 from public.staff_users s
|
|
where s.user_id = auth.uid() and coalesce(s.is_staff, false) = true
|
|
)
|
|
);
|
|
|
|
-- Fonction pour mettre à jour updated_at automatiquement
|
|
create or replace function public.handle_updated_at()
|
|
returns trigger as $$
|
|
begin
|
|
new.updated_at = now();
|
|
return new;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
-- Trigger pour mettre à jour updated_at
|
|
drop trigger if exists trigger_professions_feminisations_updated_at on public.professions_feminisations;
|
|
create trigger trigger_professions_feminisations_updated_at
|
|
before update on public.professions_feminisations
|
|
for each row execute function public.handle_updated_at(); |