49 lines
1.7 KiB
SQL
49 lines
1.7 KiB
SQL
-- Migration 190: Align sag_sager status constraint with current case status model
|
|
-- Fixes PATCH /api/v1/sag/{id} failures when using statuses beyond 'åben'/'lukket'.
|
|
|
|
DO $$
|
|
DECLARE
|
|
constraint_row RECORD;
|
|
BEGIN
|
|
-- Drop legacy check constraints on sag_sager.status regardless of their generated name.
|
|
FOR constraint_row IN
|
|
SELECT c.conname
|
|
FROM pg_constraint c
|
|
JOIN pg_class t ON t.oid = c.conrelid
|
|
JOIN pg_namespace n ON n.oid = t.relnamespace
|
|
WHERE n.nspname = 'public'
|
|
AND t.relname = 'sag_sager'
|
|
AND c.contype = 'c'
|
|
AND pg_get_constraintdef(c.oid) ILIKE '%status%'
|
|
LOOP
|
|
EXECUTE format('ALTER TABLE public.sag_sager DROP CONSTRAINT IF EXISTS %I', constraint_row.conname);
|
|
END LOOP;
|
|
END $$;
|
|
|
|
UPDATE public.sag_sager
|
|
SET status = CASE
|
|
WHEN lower(trim(status)) IN ('aaben', 'open') THEN 'åben'
|
|
WHEN lower(trim(status)) IN ('i_gang', 'in_progress', 'under behandling') THEN 'under behandling'
|
|
WHEN lower(trim(status)) IN ('on_hold', 'waiting', 'afventer') THEN 'afventer'
|
|
WHEN lower(trim(status)) IN ('resolved', 'løst') THEN 'løst'
|
|
WHEN lower(trim(status)) IN ('closed', 'afsluttet', 'lukket') THEN 'lukket'
|
|
ELSE status
|
|
END;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_constraint c
|
|
JOIN pg_class t ON t.oid = c.conrelid
|
|
JOIN pg_namespace n ON n.oid = t.relnamespace
|
|
WHERE n.nspname = 'public'
|
|
AND t.relname = 'sag_sager'
|
|
AND c.conname = 'sag_sager_status_check'
|
|
) THEN
|
|
ALTER TABLE public.sag_sager
|
|
ADD CONSTRAINT sag_sager_status_check
|
|
CHECK (status IN ('åben', 'under behandling', 'afventer', 'løst', 'lukket'));
|
|
END IF;
|
|
END $$;
|