exclude_ii_pg.txt

-- DROP PROCEDURE schema_xyz.exclude_ii_pg(text);

CREATE OR REPLACE PROCEDURE schema_xyz.exclude_ii_pg(input_table text)
 LANGUAGE plpgsql
AS $procedure$
	BEGIN

DECLARE

ec cursor is SELECT epid, uid_, pattern_nf, pattern_city from schema_xyz.exclude_pattern;
/* exclude things are deleted and a table _ex is created to save the excude results. input table must contain adr_id and top.
*/
  exclude_pattern RECORD;

BEGIN

EXECUTE 'alter table '||input_table||' add column exclude bigint, add column exclude_id bigint';

for exclude_pattern in ec

  LOOP
   
   EXECUTE 'update '||input_table||' set exclude=1, exclude_id='||exclude_pattern.epid|| '
   where (
   top='||exclude_pattern.uid_||' 
   and adr_id in (select adr_id from schema_xyz.result_II where 
   upper(address_full) like '''||exclude_pattern.pattern_nf||''' and (city is null or upper(city) like '''||exclude_pattern.pattern_city||''')))';

  END LOOP;


EXECUTE 'drop table if exists '||input_table||'_ex';
EXECUTE 'create table '||input_table||'_ex as (select * from '||input_table||')';

EXECUTE 'delete from '||input_table||' where exclude=1';
commit;

END;
	END;
$procedure$
;