Mercurial > gemma
view schema/updates/1308/01.add_section_country.sql @ 5560:f2204f91d286
Join the log lines of imports to the log exports to recover data from them.
Used in SR export to extract information that where in the meta json
but now are only found in the log.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 09 Feb 2022 18:34:40 +0100 |
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));