Mercurial > gemma
view schema/updates/1308/01.add_section_country.sql @ 5132:8d5e3ce27d20
client: Time based search
* Add Time parameter to the search request when Time slider is active
author | Fadi Abbud <fadi.abbud@intevation.de> |
---|---|
date | Fri, 27 Mar 2020 15:30:15 +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));