Versions Compared

Version Old Version 2 New Version Current
Changes made by

Serhii_Nosko

Aivar Iusupov

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
languagesql
titleDelete duplicated mapping rules
DELETE FROM
	diku_mod_source_record_manager.mapping_rules a
	USING diku_mod_source_record_manager.mapping_rules b
	WHERE a.id < b.id AND a.record_type = b.record_type
RETURNING a.id, a.record_type;

...

Code Block
languagesql
titleDelete duplicated from mapping rules
CREATE OR REPLACE FUNCTION delete_mapping_rules_duplicates(university text) RETURNS TABLE(id uuid, record_type text) AS $$
DECLARE
    module_name text := university || '_mod_source_record_manager';
BEGIN
RETURN QUERY
    EXECUTE format('DELETE FROM
                         %s.mapping_rules a
                         USING %s.mapping_rules b
                    WHERE a.id < b.id AND a.record_type = b.record_type
                    RETURNING a.id, a.record_type::text;', module_name, module_name);
    END;
$$ LANGUAGE 'plpgsql';

Function will be created on public schema.

To call function, use, for example, SELECT delete_mapping_rules_duplicates('diku'); where 'diku' is a tenant.