mCJ9HN1s

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				('InventoryOKS')) as t(alias))
23		loop
24			insert into public.tmp_id_report(id,scheme,alias,inven_note)
25			select su.key,vsMO,t.alias,su.inven_note
26				from d_la_spatial_unit su
27					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
28			where su.key=su.master_key and su.is_draft=0 and su.sys_status=0 
29				and nullif(trim(INVEN_OKS_IDENT),'') is null 
30				and coalesce(trim(upper(inven_note)),'')!~'ИДЕНТИФИКАТОР:\s*\d+';
31		end loop;
32	end loop;
33end$$;
34/*
35--Всё
36select * from public.tmp_id_report order by scheme,alias,id
37--Не пустые
38select * from public.tmp_id_report where nullif(trim(inven_note),'') is not null order by scheme,alias,id
39*/