Mercurial > gemma
view schema/updates/1105/01.improve_rls_performance.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 | 64cd18281c76 |
children |
line wrap: on
line source
CREATE OR REPLACE FUNCTION import.is_new_key( tablename varchar, kv anyelement) RETURNS boolean AS $$ DECLARE columnname varchar; DECLARE ret boolean; BEGIN columnname = (SELECT column_name FROM information_schema.constraint_column_usage k JOIN information_schema.table_constraints USING (constraint_name) WHERE k.table_name = tablename and constraint_type = 'PRIMARY KEY'); EXECUTE format('SELECT NOT EXISTS(SELECT 1 FROM import.%I WHERE %I = $1)', tablename, columnname) INTO ret USING kv; RETURN ret; END; $$ LANGUAGE plpgsql SECURITY DEFINER STABLE PARALLEL SAFE; DROP POLICY parent_allowed ON import.import_logs; CREATE POLICY parent_allowed ON import.import_logs FOR ALL TO waterway_admin USING (EXISTS(SELECT 1 FROM import.imports WHERE id = import_id)) WITH CHECK (import.is_new_key('imports', import_id) OR EXISTS(SELECT 1 FROM import.imports WHERE id = import_id)); DROP POLICY parent_allowed ON import.track_imports; CREATE POLICY parent_allowed ON import.track_imports FOR ALL TO waterway_admin USING (EXISTS(SELECT 1 FROM import.imports WHERE id = import_id)) WITH CHECK (import.is_new_key('imports', import_id) OR EXISTS(SELECT 1 FROM import.imports WHERE id = import_id)); DROP POLICY import_configuration_policy ON import.import_configuration; CREATE POLICY import_configuration_policy ON import.import_configuration FOR ALL TO waterway_admin USING (EXISTS(SELECT 1 FROM users.list_users lu WHERE lu.username = import_configuration.username)); DROP POLICY parent_allowed ON import.import_configuration_attributes; CREATE POLICY parent_allowed ON import.import_configuration_attributes FOR ALL TO waterway_admin USING (EXISTS(SELECT 1 FROM import.import_configuration WHERE id = import_configuration_id)) WITH CHECK ( import.is_new_key('import_configuration', import_configuration_id) OR EXISTS(SELECT 1 FROM import.import_configuration WHERE id = import_configuration_id));