TVT3kb1g

1do
2$$
3declare
4	cnt bigint;
5	cntAll bigint;
6	cntEmpty bigint;
7	cntNotCorrect bigint;
8	vsMO varchar(20);
9	projectRec record;
10	aliasRec record;
11begin
12	drop table if exists public.tmp_id_report;
13	create table public.tmp_id_report(id bigserial,scheme varchar(20),alias varchar(100),inven_note text);
14
15	for projectRec in(select scheme from regadm.m_projects where lower(scheme) not in('regadm','dbo','usah_reg') order by scheme)
16	loop
17		vsMO:=projectRec.scheme;
18		execute 'set search_path='||vsMO;
19		raise info '%',vsMO;
20		for aliasRec in(select alias from 
21				(values
22				('InventoryAgriculturalLands'),
23				('InventoryCommunications'),
24				('InventoryCommunicationsLine'),
25				('InventoryDrainageSystems'),
26				('InventoryHorticulturalAssociations'),
27				('InventoryHydro'),
28				('InventoryHydroLine'),
29				('InventoryLandPortion'),
30				('InventoryRedistrFund'),
31				('InventorySoilFertility'),
32				('InventorySUpassport'),
33				('InventorySUPrice')) as t(alias))
34		loop
35			insert into public.tmp_id_report(id,scheme,alias,inven_note)
36			select su.key,vsMO,t.alias,su.inven_note
37				from d_la_spatial_unit su
38					join d_ref_la_spatial_unit_type t on su.la_spatial_unit_type_key=t.key and t.key=t.master_key and t.is_draft=0 and t.sys_status=0 and t.alias=aliasRec.alias
39			where su.key=su.master_key and su.is_draft=0 and su.sys_status=0 
40				and nullif(trim(INVEN_IDENTIFIER),'') is null 
41				and coalesce(trim(upper(inven_note)),'')!~'ИДЕНТИФИКАТОР:\s*\d+';
42		end loop;
43	end loop;
44end$$;
45/*
46--Всё
47select * from public.tmp_id_report order by scheme,alias,id
48--Не пустые
49select * from public.tmp_id_report where nullif(trim(inven_note),'') is not null order by scheme,alias,id
50*/