21 lines
883 B
SQL
21 lines
883 B
SQL
-- Höchste erreichte kirchliche Hierarchiestufe (Laufbahn), nicht zurücksetzen bei Amtsverlust.
|
|
-- Siehe docs/FALUKANT_CHURCH_DAEMON.md
|
|
|
|
ALTER TABLE falukant_data.character
|
|
ADD COLUMN IF NOT EXISTS highest_church_hierarchy_ever SMALLINT;
|
|
|
|
COMMENT ON COLUMN falukant_data.character.highest_church_hierarchy_ever IS
|
|
'Max. hierarchy_level (church_office_type) jemals erreicht; für Bewerbungsvoraussetzungen neben aktuellem Amt';
|
|
|
|
UPDATE falukant_data.character c
|
|
SET highest_church_hierarchy_ever = sub.mh::smallint
|
|
FROM (
|
|
SELECT co.character_id AS cid,
|
|
MAX(cot.hierarchy_level)::int AS mh
|
|
FROM falukant_data.church_office co
|
|
JOIN falukant_type.church_office_type cot ON cot.id = co.office_type_id
|
|
GROUP BY co.character_id
|
|
) sub
|
|
WHERE c.id = sub.cid
|
|
AND (c.highest_church_hierarchy_ever IS NULL OR c.highest_church_hierarchy_ever < sub.mh);
|