Mercurial > gemma
view schema/updates/1308/01.add_section_country.sql @ 5618:57c655b93ba8 erdms2
Suppress namespace for erdms.GetRisDataXML SOAP call args.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Mon, 28 Nov 2022 17:11:43 +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));