Mercurial > gemma
view schema/updates/1308/01.add_section_country.sql @ 5721:0500d76e074b uploadwg
Fixed eraseObsoleteGaugesSQL by reintroducing matching on users CC.
The condition was removed when introducing file upload, but that leads
to too many gauges being removed.
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Fri, 19 Apr 2024 16:52:14 +0200 |
parents | 2440d2f86f4e |
children |
line wrap: on
line source
CREATE FUNCTION users.user_country(user_name name DEFAULT current_user) RETURNS internal.user_profiles.country%TYPE AS $$ SELECT country FROM users.list_users WHERE username = user_name $$ LANGUAGE SQL STABLE PARALLEL SAFE; ALTER TABLE waterway.sections ADD country char(2) REFERENCES countries DEFAULT users.user_country(); -- Try to find out the country of existing sections: UPDATE waterway.sections s SET country = ( SELECT COALESCE( users.user_country(( SELECT i.username FROM import.imports i JOIN import.import_logs ON i.id = import_id WHERE i.kind = 'sec' AND (state = 'accepted' AND s.staging_done OR state = 'pending' AND NOT s.staging_done) AND msg = 'Storing section ''' || s.name || '''' ORDER BY i.enqueued DESC FETCH FIRST ROW ONLY)), '--')); ALTER TABLE waterway.sections ALTER country SET NOT NULL; DROP POLICY responsibility_area ON waterway.sections; CREATE POLICY same_country ON waterway.sections FOR ALL TO waterway_admin USING (country = ( SELECT country FROM users.list_users WHERE username = current_user));