--предварительный просчет таблиц где необходимо менять данные
/* #count #nocount 
select count(1) from tmp.gpp_kladr_link;
*/

do $$
declare
    adr_klad record;
    p_error_code varchar = null;
    p_error_message varchar = null;
begin	

	raise notice ' Подсчитываем данные по населенным пунктам %', 1;

	drop table if exists tmp.gpp_kladr_link;
	
	create table tmp.gpp_kladr_link  as select parent_schema||'.'||parent_table tablename, parent_column from dbo.getforeignkey(null,'dbo.klarea') where parent_schema<>'integra';
	
	alter table tmp.gpp_kladr_link add column npp serial;
	
	alter table tmp.gpp_kladr_link add column kol bigint;
	
	for adr_klad in		
		select tablename,parent_column,npp from tmp.gpp_kladr_link
	LOOP
		EXECUTE 'update tmp.gpp_kladr_link set kol= '||
				'(select count(1) from '||adr_klad.tablename||' where '||adr_klad.parent_column||' in  ( '||
				'select o.klarea_id as old_id  from tmp.gpp_kladr_altnames a '||
				'inner join klarea o on a.oldcode = o.kladr_code '||
				'inner join klarea n on a.newcode = n.kladr_code '||
				'left join dbo.kladrhistory hist on kladrhistory_insdt > now()::date-1 '||
				'and hist.old_id = o.klarea_id and hist.new_id = n.klarea_id and hist.objecttype_id=0 '||
				'where 	(left(a.oldcode, 2)::int = dbo.getregion() or left(a.newcode, 2)::int = dbo.getregion()) '||
				'and o.klarea_id <> n.klarea_id and hist.kladrhistory_id is null ) ) '||
				'where npp='||adr_klad.npp::varchar;
	end LOOP;

	drop table if exists tmp.gpp_klstreet_link;
	
	create table tmp.gpp_klstreet_link  as select parent_schema||'.'||parent_table tablename, parent_column from dbo.getforeignkey(null,'dbo.klstreet');
	
	alter table tmp.gpp_klstreet_link add column npp serial;
	
	alter table tmp.gpp_klstreet_link add column kol bigint;
	
	raise notice ' Подсчитываем данные по улицам %', 2; 
	
	for adr_klad in		
		select tablename,parent_column,npp from tmp.gpp_klstreet_link
	LOOP
		EXECUTE 'update tmp.gpp_klstreet_link set kol= '||
				'(select count(1) from '||adr_klad.tablename||' where '||adr_klad.parent_column||' in  ( '||
				'select o.klarea_id as old_id  from tmp.gpp_kladr_altnames a '||
				'inner join klstreet o on a.oldcode = o.kladr_code '||
				'inner join klstreet n on a.newcode = n.kladr_code '||
				'left join dbo.kladrhistory hist on kladrhistory_insdt > now()::date-1 '||
				'and hist.old_id = o.klstreet_id and hist.new_id = n.klstreet_id and hist.objecttype_id=1 '||
				'where 	(left(a.oldcode, 2)::int = dbo.getregion() or left(a.newcode, 2)::int = dbo.getregion()) '||
				'and o.klstreet_id <> n.klstreet_id and hist.kladrhistory_id is null ) ) '||
				'where npp='||adr_klad.npp::varchar;
	end LOOP;
	--commit;	
	exception when others then
		raise notice ' Ошибка: %', SQLSTATE; 
		raise notice ' Ошибка: %', SQLERRM;
end $$
