CREATE OR REPLACE FUNCTION return_attribute_with_most_distinct_values (qi_attributes character varying[], target_schema_name varchar, target_table_name varchar) RETURNS varchar LANGUAGE plpgsql STRICT AS $$ DECLARE attribute_with_most_distinct_values varchar; temp_num integer; BEGIN temp_num:=0; for counter in 1..array_length(qi_attributes,1) loop if (return_attr_distinct_count(qi_attributes[counter], target_schema_name, target_table_name)>temp_num) then temp_num:=return_attr_distinct_count(qi_attributes[counter], target_schema_name,target_table_name); attribute_with_most_distinct_values:=qi_attributes[counter]; end if; end loop; return attribute_with_most_distinct_values; END; $$; CREATE OR REPLACE FUNCTION return_lowest_freq_nbr(qi_attributes varchar[], target_schema_name varchar, target_table_name varchar) RETURNS integer LANGUAGE plpgsql STRICT AS $$ DECLARE k_param integer; qi varchar; BEGIN qi:= array_to_string(qi_attributes, ',', '*'); EXECUTE 'SELECT min(counter) from (select '|| qi ||', count(*) AS counter from '||target_schema_name||'.'||target_table_name ||' b group by '|| qi ||')sub' INTO k_param USING qi, target_schema_name, target_table_name; return k_param; END; $$; CREATE OR REPLACE FUNCTION return_attr_distinct_count(attribute_name varchar, target_schema_name varchar, target_table_name varchar) RETURNS integer LANGUAGE plpgsql STRICT AS $$ DECLARE distinct_count integer; BEGIN EXECUTE 'SELECT COUNT(DISTINCT ' || attribute_name ||') FROM '||target_schema_name||'.'||target_table_name INTO distinct_count USING distinct_count; RETURN distinct_count; END; $$; CREATE OR REPLACE FUNCTION init_datafly(k integer, sch_name varchar, tbl_name varchar, target_sch_name varchar, target_view varchar, test_mode bool, is_triggered bool default false) RETURNS void language plpgsql AS $$ DECLARE qi_attributes varchar[]; counter integer; exist bool; BEGIN EXECUTE 'SELECT EXISTS (select 1 FROM '||sch_name||'.generalization_config gc INNER JOIN '||sch_name||'.original_and_anonymized_objects an ON gc.original_and_anonymized_objects_id=an.id where table_name = '''||tbl_name||''' and schema_name='''||sch_name||''' and target_view_name='''||target_view||''' and target_schema_name='''||target_sch_name||''')' INTO exist; IF (exist IS FALSE) THEN RAISE EXCEPTION 'Specified tables are not configured'; END IF; EXECUTE' select array_agg(attr) from (select distinct attr FROM '||sch_name||'.generalization_config gc INNER JOIN '||sch_name||'.original_and_anonymized_objects an ON gc.original_and_anonymized_objects_id=an.id where table_name = '''||tbl_name||''' and schema_name='''||sch_name||''' and target_view_name='''||target_view||''' and target_schema_name='''||target_sch_name||''')sub' INTO qi_attributes; target_view:= generate_init_view( sch_name , tbl_name, target_sch_name,target_view, test_mode, is_triggered); counter:=return_lowest_freq_nbr(qi_attributes, target_sch_name, target_view); WHILE counter target_view) THEN EXECUTE 'INSERT INTO '||sch_name||'.original_and_anonymized_objects(schema_name,table_name,target_schema_name,target_view_name) SELECT schema_name, table_name, target_schema_name, '''||final_view_name||''' FROM '||sch_name||'.original_and_anonymized_objects at WHERE at.schema_name = '''||sch_name||''' and at.table_name='''||tbl_name||''' and at.target_schema_name = '''||target_sch_name||''' and at.target_view_name='''||target_view||''''; EXECUTE'INSERT INTO '||sch_name||'.generalization_config (lvl,original_and_anonymized_objects_id, attr, generalization_rule, is_active, function) SELECT lvl, (SELECT id FROM '||sch_name||'.original_and_anonymized_objects WHERE schema_name = '''||sch_name||''' and table_name = '''||tbl_name||''' and target_schema_name = '''||target_sch_name||''' and target_view_name = '''||final_view_name||'''), attr, generalization_rule, is_active, function FROM '||sch_name||'.generalization_config gc INNER JOIN '||sch_name||'.original_and_anonymized_objects at ON gc.original_and_anonymized_objects_id=at.id WHERE schema_name = '''||sch_name||''' and table_name='''||tbl_name||''' and target_schema_name = '''||target_sch_name||''' and target_view_name = '''||target_view||''''; EXECUTE 'UPDATE '||sch_name||'.generalization_config SET is_active = false WHERE original_and_anonymized_objects_id=(SELECT id FROM '||sch_name||'.original_and_anonymized_objects WHERE schema_name = '''||sch_name||''' and table_name = '''||tbl_name||''' and target_schema_name = '''||target_sch_name||''' and target_view_name = '''||final_view_name||''') and lvl <> 0'; END IF; return final_view_name; ELSE IF (is_triggered IS FALSE) THEN IF (does_table_exist(target_sch_name,final_view_name) IS TRUE) THEN EXECUTE 'DROP VIEW '|| target_sch_name||'.'||target_view; EXECUTE 'UPDATE '||sch_name||'.generalization_config SET is_active = false WHERE original_and_anonymized_objects_id=(SELECT id FROM '||sch_name||'.original_and_anonymized_objects WHERE schema_name = '''||sch_name||''' and table_name = '''||tbl_name||''' and target_schema_name = '''||target_sch_name||''' and target_view_name = '''||final_view_name||''') and lvl <> 0'; END IF; EXECUTE 'CREATE OR REPLACE VIEW '||target_sch_name||'.'||target_view||' AS SELECT * FROM '||sch_name||'.'||tbl_name; END IF; return final_view_name; END IF; END; $$; CREATE OR REPLACE FUNCTION init_datafly_tg() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN PERFORM init_datafly( CAST (TG_ARGV[0] AS INTEGER),TG_ARGV[1],TG_ARGV[2],TG_ARGV[3],TG_ARGV[4], false, true); RETURN NEW; END; $$; CREATE OR REPLACE FUNCTION generate_triggers (k_param integer,schema_name varchar, table_name varchar, target_schema_name varchar, target_table_name varchar) returns void language plpgsql AS $$ DECLARE BEGIN EXECUTE 'DROP TRIGGER IF EXISTS '||target_schema_name||'_'||target_table_name ||'_insert_trigger ON '||schema_name||'.'||table_name; EXECUTE 'DROP TRIGGER IF EXISTS '||target_schema_name||'_'||target_table_name ||'_update_trigger ON '||schema_name||'.'||table_name; EXECUTE 'DROP TRIGGER IF EXISTS '||target_schema_name||'_'||target_table_name ||'_delete_trigger ON '||schema_name||'.'||table_name; EXECUTE 'CREATE TRIGGER '||target_schema_name||'_'||target_table_name ||'_insert_trigger AFTER INSERT ON '||schema_name||'.'||table_name || ' EXECUTE FUNCTION init_datafly_tg('||k_param||','||schema_name||','||table_name||','||target_schema_name||','||target_table_name||')'; EXECUTE 'CREATE TRIGGER '||target_schema_name||'_'||target_table_name ||'_delete_trigger AFTER DELETE ON '||schema_name||'.'||table_name || ' EXECUTE FUNCTION init_datafly_tg('||k_param||','||schema_name||','||table_name||','||target_schema_name||','||target_table_name||')'; EXECUTE 'CREATE TRIGGER '||target_schema_name||'_'||target_table_name ||'_update_trigger AFTER UPDATE ON '||schema_name||'.'||table_name || ' EXECUTE FUNCTION init_datafly_tg('||k_param||','||schema_name||','||table_name||','||target_schema_name||','||target_table_name||')'; END; $$; CREATE OR REPLACE FUNCTION generalize(attribute_name varchar, target_sch_name varchar, target_view varchar, sch_name varchar, tbl_name varchar) returns void language plpgsql AS $$ DECLARE g_rule varchar; new_level integer; view_definition varchar; current_data_type_id varchar; generalization_function varchar; caster varchar; previous_function_definition varchar; final_function_with_parameters varchar; previous_generalization_rule varchar; BEGIN caster:=''; current_data_type_id := (SELECT atttypid FROM pg_class c INNER JOIN pg_attribute a ON c.oid=a.attrelid INNER JOIN pg_namespace b ON c.relnamespace=b.oid WHERE relname=tbl_name AND attname=attribute_name and nspname=sch_name); CASE WHEN current_data_type_id IN('701','3906','23') THEN caster:='::numeric'; ELSE caster:=''; END CASE; view_definition := REPLACE((select pg_get_viewdef(target_sch_name||'.'||target_view, true)),'::text',''); view_definition := REPLACE(view_definition,'::character varying',''); EXECUTE 'SELECT function, generalization_rule,lvl from '||sch_name||'.generalization_config where attr='''||attribute_name||''' and lvl=(select min(lvl) from generalization_config where attr='''||attribute_name||''' and original_and_anonymized_objects_id=(SELECT id FROM '||sch_name||'.original_and_anonymized_objects WHERE schema_name = '''||sch_name||''' and table_name = '''||tbl_name||''' and target_schema_name = '''||target_sch_name||''' and target_view_name = '''||target_view||''') and is_active=false)and original_and_anonymized_objects_id=(SELECT id FROM '||sch_name||'.original_and_anonymized_objects WHERE schema_name = '''||sch_name||''' and table_name = '''||tbl_name||''' and target_schema_name = '''||target_sch_name||''' and target_view_name = '''||target_view||''')' INTO generalization_function,g_rule, new_level; RAISE WARNING 'Column ''%'' is being generalized with rule %', attribute_name, g_rule; IF new_level IS NULL THEN RAISE EXCEPTION 'Not enough generalization levels for column ''%'' % % % % % % ', attribute_name, g_rule, new_level, target_sch_name , target_view , sch_name , tbl_name ; END IF; EXECUTE 'SELECT function, generalization_rule from '||sch_name||'.generalization_config where attr='''||attribute_name||''' and lvl=(select max(lvl) from '||sch_name||'.generalization_config where attr='''||attribute_name||''' and original_and_anonymized_objects_id=(SELECT id FROM '||sch_name||'.original_and_anonymized_objects WHERE schema_name = '''||sch_name||''' and table_name = '''||tbl_name||''' and target_schema_name = '''||target_sch_name||''' and target_view_name = '''||target_view||''') and is_active=true)and original_and_anonymized_objects_id=(SELECT id FROM '||sch_name||'.original_and_anonymized_objects WHERE schema_name = '''||sch_name||''' and table_name = '''||tbl_name||''' and target_schema_name = '''||target_sch_name||''' and target_view_name = '''||target_view||''')' INTO previous_function_definition,previous_generalization_rule; IF new_level <> 1 THEN previous_function_definition:= previous_function_definition || '('||tbl_name||'.'|| attribute_name || caster ||', '''|| previous_generalization_rule ||''') AS ' ||attribute_name; END IF; final_function_with_parameters := generalization_function || '('||tbl_name||'.'||attribute_name || caster ||', '''||g_rule ||''') AS '||attribute_name; view_definition := REPLACE (view_definition, previous_function_definition,final_function_with_parameters); EXECUTE 'DROP VIEW '|| target_sch_name||'.'||target_view; EXECUTE 'CREATE VIEW '|| target_sch_name||'.'||target_view||' AS' || view_definition::TEXT; raise notice '% % %',previous_function_definition,new_level, target_view; EXECUTE 'UPDATE '||sch_name||'.generalization_config SET is_active = true WHERE generalization_config.lvl='||new_level||' and attr='''||attribute_name||''' and original_and_anonymized_objects_id=(SELECT id FROM '||sch_name||'.original_and_anonymized_objects WHERE schema_name = '''||sch_name||''' and table_name = '''||tbl_name||''' and target_schema_name = '''||target_sch_name||''' and target_view_name = '''||target_view||''')'; END; $$; CREATE OR REPLACE FUNCTION add_level_generalization(sch_name varchar, attribute_name varchar,tbl_name varchar,generalization_rule varchar, new_level integer,function varchar, target_sch_name varchar, target_view varchar, re_init_anon bool) RETURNS void LANGUAGE plpgsql STRICT AS $$ DECLARE k integer; exist bool; BEGIN IF (does_table_exist(sch_name, tbl_name) IS false) THEN RAISE EXCEPTION 'Table % in schema % does not exist', tbl_name, sch_name; END IF; IF (does_column_exist_in_table(attribute_name, sch_name, tbl_name) IS FALSE) THEN RAISE EXCEPTION 'Attribute % in table % does not exist', attribute_name, tbl_name; END IF; IF (check_if_generalization_rule_exists(attribute_name, sch_name, tbl_name, generalization_rule, target_sch_name, target_view) IS TRUE) THEN RAISE EXCEPTION 'Rule % in anonymized view % for attribute % already exists', generalization_rule, target_view, attribute_name; END IF; IF (check_if_level_exists(attribute_name, sch_name, tbl_name, new_level, target_sch_name, target_view) IS TRUE) THEN RAISE EXCEPTION 'Level % already exist in anonymized view % for attribute %', new_level, target_view, attribute_name; END IF; EXECUTE 'SELECT EXISTS (SELECT 1 from '||sch_name||'.generalization_config where attr='''||attribute_name||''')' INTO EXIST; IF (exist IS FALSE) THEN EXECUTE 'INSERT INTO '||sch_name||'.generalization_config(lvl,original_and_anonymized_objects_id, attr,generalization_rule,is_active,function) VALUES (0,(SELECT id FROM '||sch_name||'.original_and_anonymized_objects WHERE schema_name = $2 and table_name = $3 and target_schema_name = $4 and target_view_name = $5), $6,NULL,TRUE,'''||tbl_name ||'.'||attribute_name||''')' USING new_level, sch_name, tbl_name, target_sch_name, target_view, attribute_name, generalization_rule, function; END IF; EXECUTE 'INSERT INTO '||sch_name||'.generalization_config(lvl,original_and_anonymized_objects_id, attr,generalization_rule,is_active,function) VALUES ($1,(SELECT id FROM '||sch_name||'.original_and_anonymized_objects WHERE schema_name = $2 and table_name = $3 and target_schema_name = $4 and target_view_name = $5), $6,$7,FALSE,$8)' USING new_level, sch_name, tbl_name, target_sch_name, target_view, attribute_name, generalization_rule, function; IF (re_init_anon IS TRUE) THEN EXECUTE 'SELECT k_param from '||sch_name||'.original_and_anonymized_objects where schema_name = '''||sch_name||''' and table_name = '''||tbl_name||''' and target_schema_name = '''||target_sch_name||''' and target_view_name = '''||target_view||'''' into k; EXECUTE 'UPDATE '||sch_name||'.generalization_config SET is_active = FALSE WHERE original_and_anonymized_objects_id=(SELECT id FROM '||sch_name||'.original_and_anonymized_objects WHERE schema_name = $1 and table_name = $2 and target_schema_name = $3 and target_view_name = $4) and lvl <> 0' USING sch_name,tbl_name, target_sch_name, target_view; IF(k is null) THEN RAISE EXCEPTION 'There are no previous executions for specified original and target objects. You cannot re-execute datafly if it was not executed initially, please use init_datafly function'; ELSE PERFORM init_datafly(k, sch_name, tbl_name, target_sch_name, target_view, FALSE); END IF; END IF; END; $$; CREATE OR REPLACE FUNCTION check_if_level_exists(attribute_name varchar, schema_name varchar,table_name varchar, new_level integer, target_schema_name varchar, target_table_name varchar) RETURNS boolean LANGUAGE plpgsql STRICT AS $$ DECLARE exist bool; BEGIN EXECUTE 'SELECT EXISTS (SELECT 1 FROM '||schema_name||'.generalization_config gc INNER JOIN '||schema_name||'.original_and_anonymized_objects at ON gc.original_and_anonymized_objects_id=at.id WHERE gc.attr='''||attribute_name||''' and at.schema_name='''||schema_name||''' AND at.table_name = '''||table_name||''' and gc.lvl='||new_level||' AND at.target_schema_name='''||target_schema_name||''' AND at.target_view_name='''||target_table_name||''')' into exist; return exist; END; $$; CREATE OR REPLACE FUNCTION check_if_generalization_rule_exists (attribute_name varchar, schema_name varchar, table_name varchar, rule varchar, target_schema_name varchar, target_table_name varchar) RETURNS boolean LANGUAGE plpgsql STRICT AS $$ DECLARE exist bool; BEGIN EXECUTE 'SELECT EXISTS (SELECT 1 FROM '||schema_name||'.generalization_config gc INNER JOIN '||schema_name||'.original_and_anonymized_objects at on gc.original_and_anonymized_objects_id=at.id WHERE gc.attr='''||attribute_name||''' and at.schema_name ='''||schema_name||''' and at.table_name='''||table_name||''' and at.target_schema_name='''||target_schema_name||''' and at.target_view_name='''||target_table_name||''' and gc.generalization_rule='''||rule||''') IS TRUE' INTO exist; return exist; END; $$; CREATE OR REPLACE FUNCTION does_column_exist_in_table(attribute_name varchar, sch_name varchar,tbl_name varchar) returns boolean LANGUAGE plpgsql STRICT AS $$ BEGIN IF (SELECT EXISTS( SELECT FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace=n.oid INNER JOIN pg_attribute a ON c.oid=a.attrelid WHERE attname=attribute_name ) IS FALSE) THEN return false; ELSE return true; END IF; END; $$; CREATE OR REPLACE FUNCTION does_table_exist(schema_name varchar, tbl_name varchar) RETURNS boolean LANGUAGE plpgsql STRICT AS $$ BEGIN IF (SELECT EXISTS ( SELECT FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace=n.oid WHERE c.relname = tbl_name AND n.nspname = schema_name ) IS FALSE) THEN return false; ELSE return true; END IF; END; $$; CREATE OR REPLACE FUNCTION update_level_generalization(attribute_name varchar, sch_name varchar, tbl_name varchar, target_sch_name varchar, target_view varchar, generalization_rule varchar, generalization_lvl integer, re_init_anon bool default false) RETURNS void LANGUAGE plpgsql STRICT AS $$ DECLARE if_exist boolean; level_num integer; k integer; BEGIN IF (check_if_generalization_rule_exists(attribute_name, sch_name, tbl_name, generalization_rule, target_sch_name, target_view) IS TRUE) THEN RAISE EXCEPTION 'Rule % in anonymized view % for attribute % already exists', generalization_rule, target_view, attribute_name; END IF; IF (check_if_level_exists(attribute_name, sch_name, tbl_name, generalization_lvl, target_sch_name, target_view) IS FALSE) THEN RAISE EXCEPTION 'Level % does not exist in anonymized view % for attribute % or table is not configured for anonymization', generalization_lvl, target_view, attribute_name; END IF; EXECUTE 'UPDATE '||sch_name||'.generalization_config SET generalization_rule = $1 WHERE attr= $2 and lvl= $3 and original_and_anonymized_objects_id=(SELECT id FROM '||sch_name||'.original_and_anonymized_objects WHERE schema_name = $4 and table_name = $5 and target_schema_name = $6 and target_view_name = $7) ' USING generalization_rule, attribute_name, generalization_lvl,sch_name,tbl_name, target_sch_name, target_view; IF (re_init_anon IS TRUE) THEN k := (SELECT k_param from original_and_anonymized_objects where schema_name = $2 and table_name = $3 and target_schema_name = $4 and target_view_name = $5); EXECUTE 'UPDATE '||sch_name||'.generalization_config SET is_active = FALSE WHERE original_and_anonymized_objects_id=(SELECT id FROM '||sch_name||'.original_and_anonymized_objects WHERE schema_name = $1 and table_name = $2 and target_schema_name = $3 and target_view_name = $4) and lvl <> 0' USING sch_name,tbl_name, target_sch_name, target_view; IF(k is null) THEN RAISE EXCEPTION 'There are no previous executions for specified original and target objects. You cannot re-execute datafly if it was not executed initially, please use init_datafly function'; ELSE PERFORM init_datafly(k, sch_name, tbl_name, target_sch_name, target_view, FALSE); END IF; END IF; END; $$; CREATE OR REPLACE FUNCTION remove_level_generalization(attribute_name varchar, tbl_name varchar, sch_name varchar, target_sch_name varchar, target_view varchar, generalization_lvl integer,re_init_anon bool) RETURNS void LANGUAGE plpgsql STRICT AS $$ DECLARE k integer; exist bool; BEGIN IF (check_if_level_exists(attribute_name, sch_name, tbl_name, generalization_lvl, target_sch_name, target_view) IS FALSE) THEN RAISE EXCEPTION 'Level % in anonymized view % for attribute % does not exist or the objects are not configured', generalization_lvl, target_view, attribute_name; END IF; EXECUTE 'DELETE FROM '||sch_name||'.generalization_config WHERE attr= $1 and lvl= $2 and original_and_anonymized_objects_id=(SELECT id FROM '||sch_name||'.original_and_anonymized_objects WHERE schema_name = $3 and table_name = $4 and target_schema_name = $6 and target_view_name = $5)' USING attribute_name::TEXT, generalization_lvl, sch_name, tbl_name, target_view, target_sch_name; IF (re_init_anon IS TRUE) THEN EXECUTE 'SELECT k_param from '||sch_name||'.original_and_anonymized_objects where schema_name = '''||sch_name||''' and table_name = '''||tbl_name||''' and target_schema_name = '''||target_sch_name||''' and target_view_name = '''||target_view||'''' into k; EXECUTE 'UPDATE '||sch_name||'.generalization_config SET is_active = FALSE WHERE original_and_anonymized_objects_id=(SELECT id FROM '||sch_name||'.original_and_anonymized_objects WHERE schema_name = $1 and table_name = $2 and target_schema_name = $3 and target_view_name = $4) and lvl <> 0' USING sch_name,tbl_name, target_sch_name, target_view; IF(k is null) THEN RAISE EXCEPTION 'There are no previous executions for specified original and target objects. You cannot re-execute datafly if it was not executed initially, please use init_datafly function'; ELSE PERFORM init_datafly(k, sch_name, tbl_name, target_sch_name, target_view, FALSE); END IF; END IF; END; $$; CREATE OR REPLACE FUNCTION configure_plugin(json_config json) RETURNS void LANGUAGE plpgsql STRICT AS $$ DECLARE sch_name varchar; tbl_name varchar; target_sch_name varchar; target_view_name varchar; quasi_identifiers json; quasi_identifier_name varchar; quasi_identifiers_generalization json; BEGIN sch_name := json_config ->>'schName'; tbl_name := json_config ->>'tblName'; target_sch_name := json_config ->>'targetSchemaName'; target_view_name := json_config ->>'targetViewName'; quasi_identifiers := json_config->'quasiIdentifiers'; IF(does_table_exist(sch_name,'original_and_anonymized_objects') IS FALSE) THEN EXECUTE 'CREATE TABLE ' || sch_name || '.original_and_anonymized_objects (id SERIAL PRIMARY KEY, schema_name varchar, table_name varchar, target_schema_name varchar, target_view_name varchar, k_param integer)'; END IF; IF(does_table_exist(sch_name,'generalization_config') IS FALSE) THEN EXECUTE 'create table '|| sch_name ||'.generalization_config (lvl integer, original_and_anonymized_objects_id integer, attr varchar, generalization_rule VARCHAR, is_active boolean, function varchar, PRIMARY KEY (lvl,attr,original_and_anonymized_objects_id), CONSTRAINT fk_target_and_source_objects FOREIGN KEY(original_and_anonymized_objects_id) REFERENCES '|| sch_name||'.original_and_anonymized_objects(id))'; END IF; IF(sch_name is null or tbl_name is null or target_sch_name is null or target_view_name is null or quasi_identifiers is null) THEN RAISE EXCEPTION 'Json is not set correctly'; END IF; IF(does_table_exist(sch_name,tbl_name) IS FALSE) THEN RAISE EXCEPTION 'Table % does not exist in schema %', tbl_name, sch_name; END IF; EXECUTE 'INSERT INTO '||sch_name||'.original_and_anonymized_objects(schema_name,table_name,target_schema_name,target_view_name) values ('''||sch_name||''','''||tbl_name||''','''||target_sch_name||''','''||target_view_name||''')'; for counter in 0 .. json_array_length(quasi_identifiers)-1 loop quasi_identifier_name := quasi_identifiers->counter->>'attrName'; quasi_identifiers_generalization := quasi_identifiers->counter ->>'generalizationConfiguration'; for counter in 0 ..json_array_length(quasi_identifiers_generalization)-1 loop IF (quasi_identifier_name is null or quasi_identifiers_generalization->counter->>'generalizationRule' is null or quasi_identifiers_generalization->counter->>'level' is null or quasi_identifiers_generalization->counter->>'generalizationFunction' is null or quasi_identifiers_generalization->counter->>'generalizationFunction' not in ('generalize_numrange', 'generalize_daterange') ) then RAISE EXCEPTION 'Json is not set correctly'; END IF; perform add_level_generalization(sch_name,quasi_identifier_name ,tbl_name, quasi_identifiers_generalization->counter->>'generalizationRule'::VARCHAR, CAST(quasi_identifiers_generalization->counter->>'level' AS INTEGER), quasi_identifiers_generalization->counter->>'generalizationFunction'::VARCHAR,target_sch_name,target_view_name,false); end loop; end loop; END; $$; CREATE OR REPLACE FUNCTION generalize_numrange( val NUMERIC, step VARCHAR ) RETURNS NUMRANGE AS $$ WITH i AS ( SELECT int4range( val::INTEGER / step::INTEGER * step::INTEGER, ((val::INTEGER / step::INTEGER)+1) * step::INTEGER ) as r ) SELECT numrange( lower(i.r)::NUMERIC, upper(i.r)::NUMERIC ) FROM i ; $$ LANGUAGE SQL IMMUTABLE SECURITY INVOKER; CREATE OR REPLACE FUNCTION generalize_daterange( val DATE, step TEXT DEFAULT 'decade' ) RETURNS DATERANGE AS $$ SELECT daterange( date_trunc(step, val)::DATE, (date_trunc(step, val) + ('1 '|| step)::INTERVAL)::DATE ); $$ LANGUAGE SQL IMMUTABLE SECURITY INVOKER;