https://pastein.ru/t/cKg
Загрузка данных
CREATE OR REPLACE FUNCTION ourpension.f_reconciliation_of_operations(db date, de date, p_fund_id integer DEFAULT 1)
RETURNS TABLE(num integer, title text, beg numeric, pfr_in numeric, npf_in numeric, other_in numeric, total_in numeric, pension numeric, assignee numeric, pens_ass numeric, pfr_out numeric, npf_out numeric, total_out numeric, returns numeric, acc_red numeric, acc_res_red numeric, gar numeric, id numeric, ops_to_pds numeric, comission numeric, from_rops_to_ops_pds numeric, "end" numeric, total_cr numeric, total_db numeric, total_cr_db numeric, "check" numeric)
LANGUAGE plpgsql
SET plan_cache_mode TO 'force_custom_plan'
SET work_mem TO '256MB'
SET hash_mem_multiplier TO '2'
SET jit TO 'off'
AS $function$
begin
drop table if exists selected_funds;
drop table if exists temp_transactions;
drop table if exists acc_types_agg_in_group_temp;
drop table if exists oper_types_agg_in_group_temp;
drop table if exists balance;
drop table if exists pg_temp.balance_relevant;
create temp table if not exists acc_types_agg_in_group_temp
on commit drop as
with t(group_num , group_type , type_in , type_out, cross_link ) as (
values
(1,'Н',null,null,null),--Накопление
(1,'Р(Е)',null,null,null),--Резерв единовременных выплат
-- (1,null,'НВ ОПС',null,null),--Начисление выплат (Страховые взносы)
-- (1,null,'НВ ДПС Г',null,'{2,3,4}'),--Начисление выплат (Взносы софинансирования)
-- (1,null,'НВ ДПС Л',null,'{2,3,4}'),--Начисление выплат (Добровольные страховые взносы)
-- (1,null,'НВ ДПС МК',null,null),--Начисление выплат (Материнский капитал)
-- (1,null,'НВ ПП ДПС МК',null,null),--Начисление выплат правопреемникам (Материнский капитал)
(2,'Н',null,'{ДПС МК,ДПС МК ИД}',null),
(2,'Р(Е)',null,'{Р(Е) ДПС МК,Р(Е) ДПС МК ИД}',null),
-- (2,null,'НВ ОПС',null,null),
-- (2,null,'НВ ДПС Г',null,'{2,3,4}'),
-- (2,null,'НВ ДПС Л',null,'{2,3,4}'),
-- (3,null,'НВ ДПС МК',null,null),
(3,null,'ДПС МК',null,null),--Материнский капитал
(3,null,'ДПС МК ИД',null,null),
(3,null,'Р(Е) ДПС МК',null,null),--Резерв единовременных выплат (Материнский капитал)
(3,null,'Р(Е) ДПС МК ИД',null,null),
(4,'Р ПП',null,null,null),--Резерв выплат правопреемникам
-- (4,null,'НВ ПП ОПС',null,null),--Начисление выплат правопреемникам (Страховые взносы)
-- (4,'НВ ПП',null,null,null),--Начисление выплат правопреемникам
-- (6,null,'НВ ДПС Г',null,'{1}'),
-- (6,null,'НВ ДПС Л',null,'{1}'),
-- (6,null,'Р(С)',null,'{1}'),--Резерв срочных выплат (мнемоника типа счета или подгруппы?)
-- (6,null,'Р(С) ИД',null,'{1}'),
(6,null,'Р(С) ОПС',null,'{1}'),
(6,null,'Р(С) ОПС ИД',null,'{1}'),
(6,null,'Р(С) ДПС Л',null,'{1}'),
(6,null,'Р(С) ДПС Л ИД',null,'{1}'),
(6,null,'Р(С) ДПС Р',null,'{1}'),
(6,null,'Р(С) ДПС Р ИД',null,'{1}'),
(6,null,'Р(С) ДПС Г',null,'{1}'),
(6,null,'Р(С) ДПС Г ИД',null,'{1}'),
(7,null,'НВ ДПС МК',null,'{1}'),
(7,null,'НВ ПП ДПС МК',null,'{1}'),
(7,null,'Р(С) ДПС МК',null,'{1}'),
(7,null,'Р(С) ДПС МК ИД',null,'{1}'),
(9,'Р(П)',null,null,null),--Резерв пожизненных выплат (мнемоника типа счета или подгруппы?)
(10,null,'Р ОПС',null,null)--,
-- (10,null,'Р ОПС ИД',null,null)
--как перетекают деньги из мнем в мнем? Н - Р(П) ОПС - НВ ОПС - В ОПС - ВЗ (-> Р(П))
)
, tt as (
select * from t
union all
select 5, group_type , type_in , type_out, cross_link
from t
where group_num in (2,3,4)
union all
select 8, group_type , type_in , type_out, cross_link
from t
where group_num in (6,7)
)
select * from tt
union all
select 11, group_type , type_in , type_out, cross_link
from tt
where group_num in (5,8,9,10)
order by group_num, group_type , type_in , type_out, cross_link;
create temp table if not exists oper_types_agg_in_group_temp
on commit drop as
select * from (
values
(1,'PENSION_SAVINGS_ENROLLMENT',null,null),
(2,'PENSION_SAVINGS_ENROLLMENT',null,null),
(3,'MANUAL_TEMPORARY',null,null),
(3,'ENROLLMENT_OTHER',null,null),
(4,'PENSION_SAVINGS_ENROLLMENT',null,null),
(4,'MANUAL_TEMPORARY',null,null),
(4,'ENROLLMENT_OTHER',null,null),
(5,'PAYMENT_SPV',1,null),
(5,'PAYMENT_NP',2,null),
(5,'PAYMENT_EV',3,null),
(5,'ADDITIONAL_PAYMENT_EV',4,null),
(6,'PAYMENT_PP',null,null),
(6,'ADDITIONAL_PAYMENT_PP',null,null),
(7,'PAYMENT_SPV',1,null),
(7,'PAYMENT_NP',2,null),
(7,'PAYMENT_EV',3,null),
(7,'ADDITIONAL_PAYMENT_EV',4,null),
(7,'PAYMENT_PP',null,null),
(7,'ADDITIONAL_PAYMENT_PP',null,null),
(8,'TRANSFER',null,null),
(9,'TRANSFER',null,null),
(10,'TRANSFER',null,null),
(11,'RETURN',null,null),
(12,'TRANSFER_TO_ASSIGNEE_RESERVE',null,null),-- гр. Н, Р(Е), Р(ПП)
(13,'TRANSFER_TO_RESERVE',null,null),
(14,'GUARANTEE_ENROLLMENT',null,null),
(14,'GUARANTEE',null,null),
(15,'PENSION_INVESTMENT_REVENUE_ENROLLMENT',null,null),
(16,'PDS_OPS_ENROLLMENT_OPS',null,null),
(16,'PDS_TRANSFER_OPS',null,null),
(17,'COMMISSION_NP_FROM_FUND_RESERVE',null,null),
(18,'TRANSFER_TO_INSURED_FUND_RESERVE_OPS_PDS',null,null)
) t (group_num , group_type , id , type_out );
case
when db is null or de is null then
return query
select null:: integer, null:: text, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric,
null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric, null:: numeric;
else
raise notice 'start create temp table selected_funds %', clock_timestamp();
CREATE TEMP TABLE IF NOT EXISTS selected_funds
ON COMMIT DROP AS
WITH
fund_sharers AS NOT MATERIALIZED (
SELECT sh.id
FROM ourpension.contract c
JOIN ourpension.sharer sh
ON sh.contract_id = c.id
WHERE c.fund_id = p_fund_id
),
ops_types AS NOT MATERIALIZED (
SELECT
at.id,
at.mnemonics,
atg.mnemonics AS group_mnemonics
FROM back_office.account_type at
JOIN back_office.account_type_group atg
ON atg.id = at.account_type_group_id
WHERE at.service_type = 'OPS'
)
SELECT
a.id,
ot.mnemonics,
ot.group_mnemonics
FROM fund_sharers fs
JOIN back_office.account a
ON a.owner_sharer_id = fs.id
JOIN ops_types ot
ON ot.id = a.account_type_id
UNION ALL
SELECT
a.id,
at.mnemonics,
atg.mnemonics AS group_mnemonics
FROM back_office.account a
JOIN back_office.account_type at
ON at.id = a.account_type_id
JOIN back_office.account_type_group atg
ON atg.id = at.account_type_group_id
WHERE a.owner_entity_id = p_fund_id
AND a.account_type_id IN (41);
--296 федерация
CREATE INDEX selected_funds_idx ON selected_funds(id, mnemonics, group_mnemonics);
ANALYZE selected_funds; -- 24M строк: актуальная статистика критична для JOIN
raise notice 'end create temp table selected_funds %', clock_timestamp();
raise notice 'start insert into temp_transactions %', clock_timestamp();
create temp table if not exists temp_transactions on commit drop as
select *
from back_office.transaction t
where t.effective_date between $1::date and $2::date;
-- (select ca.owner_sharer_id, ca.owner_entity_id, ca.owner_individual_id, t.value acc_type_value, 'credit' direction, t.effective_date, sf.mnemonics, sf.group_mnemonics
-- from back_office."transaction" t
-- join back_office."account" ca on ca.id = t.credit_account_id
-- join selected_funds sf on sf.id = ca.id
-- join back_office.operation o on o.id = t.operation_id
-- join back_office.operation_type ot on ot.id = o.operation_type_id--01.12.2025
-- join oper_types_agg_in_group_temp temp_1 on temp_1.group_type = ot.code--01.12.2025
-- join acc_types_agg_in_group_temp acc_temp on --01.12.2025
-- case when acc_temp.group_type is null then true else acc_temp.group_type = sf.mnemonics end--01.12.2025
-- and case when acc_temp.type_in is null then true else acc_temp.type_in = sf.mnemonics end--01.12.2025
-- and case when acc_temp.type_out is null then true else sf.mnemonics <> all(acc_temp.type_out::text[]) end--01.12.2025
-- where t.effective_date <= de
-- and o.approved is not null /*Авторизована*/
-- and o.deleted is null /*Не удалена*/
-- and o.revoked is null /*Не отозвана*/
--
-- union all
--
-- select da.owner_sharer_id, da.owner_entity_id, da.owner_individual_id, -t.value acc_type_value, 'debit' direction, t.effective_date, sf.mnemonics, sf.group_mnemonics
-- from back_office."transaction" t
-- join back_office."account" da on da.id = t.debit_account_id
-- join selected_funds sf on sf.id = da.id
-- join back_office.operation o on o.id = t.operation_id
-- join back_office.operation_type ot on ot.id = o.operation_type_id--01.12.2025
-- join oper_types_agg_in_group_temp temp_1 on temp_1.group_type = ot.code--01.12.2025
-- join acc_types_agg_in_group_temp acc_temp on --01.12.2025
-- case when acc_temp.group_type is null then true else acc_temp.group_type = sf.mnemonics end--01.12.2025
-- and case when acc_temp.type_in is null then true else acc_temp.type_in = sf.mnemonics end--01.12.2025
-- and case when acc_temp.type_out is null then true else sf.mnemonics <> all(acc_temp.type_out::text[]) end--01.12.2025
-- where t.effective_date <= de
-- and o.approved is not null /*Авторизована*/
-- and o.deleted is null /*Не удалена*/
-- and o.revoked is null /*Не отозвана*/
-- );
-- CREATE INDEX temp_transactions_idx ON temp_transactions(effective_date,group_mnemonics,mnemonics,direction,acc_type_value);
raise notice 'end insert into temp_transactions %', clock_timestamp();
raise notice 'start balance %', clock_timestamp();
-- V2: сначала отбираем актуальные срезы остатков из balance,
-- а уже потом соединяем их с ~24 млн selected_funds.
-- У balance ~454 млн строк, но уникальных account_id значительно меньше.
-- Этот шаг предотвращает план с миллионами пробных обращений к balance
-- по каждому ID selected_funds. Не используем DISTINCT: сохраняем
-- все строки истории, в т.ч. возможные пересечения периодов.
raise notice 'start balance_relevant %', clock_timestamp();
CREATE TEMP TABLE balance_relevant ON COMMIT DROP AS
SELECT
b.account_id,
b.total_credit - b.total_debit AS total,
(($1::date - 1) BETWEEN b.start_date AND b.end_date) AS for_beg,
($2::date BETWEEN b.start_date AND b.end_date) AS for_end
FROM back_office.balance b
WHERE (($1::date - 1) BETWEEN b.start_date AND b.end_date)
OR ($2::date BETWEEN b.start_date AND b.end_date);
ANALYZE balance_relevant;
raise notice 'end balance_relevant %', clock_timestamp();
-- Сохраняем исходные соответствия счетов группам и кратность строк.
-- FILTER вычисляет начальный и конечный остаток одним агрегированием.
raise notice 'start balance_aggregate %', clock_timestamp();
CREATE TEMP TABLE balance ON COMMIT DROP AS
SELECT agg.mnem_gr, v.total, v."date"
FROM (
SELECT
acc_temp.group_num AS mnem_gr,
SUM(br.total) FILTER (WHERE br.for_beg) AS total_beg,
COUNT(*) FILTER (WHERE br.for_beg) AS count_beg,
SUM(br.total) FILTER (WHERE br.for_end) AS total_end,
COUNT(*) FILTER (WHERE br.for_end) AS count_end
FROM balance_relevant br
JOIN selected_funds sf
ON sf.id = br.account_id
JOIN acc_types_agg_in_group_temp acc_temp
ON (acc_temp.group_type IS NULL OR acc_temp.group_type = sf.group_mnemonics)
AND (acc_temp.type_in IS NULL OR acc_temp.type_in = sf.mnemonics)
AND (acc_temp.type_out IS NULL OR sf.mnemonics <> ALL(acc_temp.type_out::text[]))
GROUP BY acc_temp.group_num
) agg
CROSS JOIN LATERAL (
VALUES
(agg.total_beg, 'beg'::text, agg.count_beg),
(agg.total_end, 'end'::text, agg.count_end)
) v(total, "date", cnt)
WHERE v.cnt > 0;
raise notice 'end balance_aggregate %', clock_timestamp();
raise notice 'end balance %', clock_timestamp();
raise notice 'start return query start %', clock_timestamp();
return query
with anker as (
select *
from unnest ((select '{'||string_agg(n::text,',')||'}' from pg_catalog.generate_series(1,11) gs(n))::int[],
array['СПН, учтенные на счетах ЗЛ (кроме умерших ЗЛ)',
'СПН, учтенные на счетах ЗЛ (кроме МК)',
'Материнский капитал, учтенный на счетах ЗЛ',
'СПН, учтенные на счете умерших ЗЛ',
'Итого СПН, учтенные на счетах',
'Резерв СПВ (выплаты без учета МК)',
'Резерв СПВ (выплаты за счет материнского капитала)',
'Резерв СПВ',
'ВР',
'РОПС',
'Итого ПН']) gs(num,title)
)
, deposit as (--работает
select
acc_temp.group_num as mnem_gr,
string_agg(distinct sf.mnemonics,', ') ,
string_agg(distinct sf.group_mnemonics,', ') ,
t_temp.group_num as oper_gr,
sum(case when f.name_in_pfr in ('ПФР','СФР') then t.value else 0 end) pfr_in,
sum(case when f.name_in_pfr not in ('ПФР','СФР') then t.value else 0 end) npf_in,
sum(case when t_temp.group_num = '3' then t.value else 0 end) other_in,
sum(t.value) total_in
from selected_funds sf
join back_office.account da on sf.id = da.id
join temp_transactions t on t.credit_account_id = da.id
-- join back_office.account_type at on at.id = da.account_type_id
-- join back_office.account_type_group atg on at.account_type_group_id = atg.id
join acc_types_agg_in_group_temp acc_temp on
case when acc_temp.group_type is null then true else acc_temp.group_type = sf.group_mnemonics end
and case when acc_temp.type_in is null then true else sf.mnemonics = acc_temp.type_in end
and case when acc_temp.type_out is null then true else sf.mnemonics <> all(acc_temp.type_out::text[]) end
join back_office.operation o on o.id = t.operation_id
join back_office.operation_type ot on ot.id = o.operation_type_id
join oper_types_agg_in_group_temp t_temp on t_temp.group_type = ot.code and t_temp.group_num in ('1','2','3','4')
join back_office.incoming_order_operation iop on iop.operation_id = t.operation_id
join back_office.incoming_order io on iop.incoming_order_id = io.id
and io.service_type = 'OPS'
join ourpension.entity e on io.payer_entity_id = e.id
join ourpension.fund f on f.entity_id = e.id
where t.effective_date between $1::date and $2::date
and o.approved is not null /*Авторизована*/
and o.deleted is null /*Не удалена*/
and o.revoked is null /*Не отозвана*/
group by acc_temp.group_num,t_temp.group_num
order by acc_temp.group_num,t_temp.group_num
)
, transfert as (-- работает
select
acc_temp.group_num as mnem_gr,
string_agg(distinct sf.mnemonics,', ') ,
string_agg(distinct sf.group_mnemonics,', ') ,
t_temp.group_num as oper_gr,
sum(case when f.name_in_pfr in ('ПФР','СФР') then t.value else 0 end) pfr_out,
sum(case when f.name_in_pfr not in ('ПФР','СФР') then t.value else 0 end) npf_out,
sum(t.value) total_out
from selected_funds sf
join back_office.account da on sf.id = da.id
join temp_transactions t on t.debit_account_id = da.id
join acc_types_agg_in_group_temp acc_temp on
case when acc_temp.group_type is null then true else acc_temp.group_type = sf.group_mnemonics end
and case when acc_temp.type_in is null then true else sf.mnemonics = acc_temp.type_in end
and case when acc_temp.type_out is null then true else sf.mnemonics <> all(acc_temp.type_out::text[]) end
join back_office.operation o on o.id = t.operation_id
join back_office.operation_type ot on ot.id = o.operation_type_id
join oper_types_agg_in_group_temp t_temp on t_temp.group_type = ot.code and t_temp.group_num in ('8','9','10')
join back_office.account ca on t.credit_account_id = ca.id
join ourpension.entity e on ca.owner_entity_id = e.id
join ourpension.fund f on f.entity_id = e.id
where t.effective_date between $1::date and $2::date
and o.approved is not null /*Авторизована*/
and o.deleted is null /*Не удалена*/
and o.revoked is null /*Не отозвана*/
group by acc_temp.group_num,t_temp.group_num
order by acc_temp.group_num,t_temp.group_num
)
, lets_start as (
select
acc_temp.group_num as mnem_gr,
t_temp.group_num as oper_gr,
sum(case when t_temp.group_num = '5' then t.value else 0 end) pension,-- сильная разница в обе стороны
sum(case when t_temp.group_num = '6' then t.value else 0 end) assignee,
sum(case when t_temp.group_num = '7' then t.value else 0 end) pens_ass,
sum(case when t_temp.group_num = '16' then t.value else 0 end) ops_to_pds,--просто жесть а не разница
sum(case when t_temp.group_num = '17' then t.value else 0 end) comission,
sum(case when t_temp.group_num = '18' then t.value else 0 end) from_rops_to_ops_pds,
sum(t.value) sum_value
from selected_funds sf
join back_office.account da on sf.id = da.id
join temp_transactions t on t.debit_account_id = da.id
join acc_types_agg_in_group_temp acc_temp on
case when acc_temp.group_type is null then true else acc_temp.group_type = sf.group_mnemonics end
and case when acc_temp.type_in is null then true else sf.mnemonics = acc_temp.type_in end
and case when acc_temp.type_out is null then true else sf.mnemonics <> all(acc_temp.type_out::text[]) end
join back_office.operation o on o.id = t.operation_id
join back_office.operation_type ot on ot.id = o.operation_type_id
join oper_types_agg_in_group_temp t_temp on t_temp.group_type = ot.code
and t_temp.group_num in ('5','6','7','11','12','13','14','15','16','17','18')
-- and case when acc_temp.cross_link is not null then t_temp.id = any (acc_temp.cross_link::int[]) else true end
where t.effective_date between $1::date and $2::date
and o.approved is not null /*Авторизована*/
and o.deleted is null /*Не удалена*/
and o.revoked is null /*Не отозвана*/
group by acc_temp.group_num,t_temp.group_num
order by acc_temp.group_num,t_temp.group_num
)
select gen.*, gen.total_cr+gen.total_db total_cr_db, gen.acc_res_red - (gen.total_cr+gen.total_db) "check"
from
(
select gs.num, gs.title, s0.total beg,
s1.pfr_in ,s2.npf_in ,s3.other_in ,s4.total_in ,
s5.pension,s6.assignee,s7.pens_ass,
s8.pfr_out,s9.npf_out ,s10.total_out ,
s11.sum_value "returns",
s12.sum_value acc_red,s13.sum_value acc_res_red,s14.sum_value gar,s15.sum_value id, s19.ops_to_pds, s20.comission, s21.from_rops_to_ops_pds, s16.total "end",0::numeric total_cr, 0::numeric total_db--, s17.total_cr, s18.total_db
from anker gs
left join (select * from balance where "date"='beg') s0 on gs.num = s0.mnem_gr
left join (select *
from deposit
where oper_gr = 1) s1 on gs.num = s1.mnem_gr
left join (select *
from deposit
where oper_gr = 2) s2 on gs.num = s2.mnem_gr
left join (select *
from deposit
where oper_gr = 3) s3 on gs.num = s3.mnem_gr
left join (select *
from deposit
where oper_gr = 4) s4 on gs.num = s4.mnem_gr
left join (select *
from lets_start
where oper_gr = 5) s5 on gs.num = s5.mnem_gr
left join (select *
from lets_start
where oper_gr = 6) s6 on gs.num = s6.mnem_gr
left join (select *
from lets_start
where oper_gr = 7) s7 on gs.num = s7.mnem_gr
left join (select *
from transfert
where oper_gr = 8) s8 on gs.num = s8.mnem_gr
left join (select *
from transfert
where oper_gr = 9) s9 on gs.num = s9.mnem_gr
left join (select *
from transfert
where oper_gr = 10) s10 on gs.num = s10.mnem_gr
left join (select *
from lets_start
where oper_gr = 11) s11 on gs.num = s11.mnem_gr
left join (select *
from lets_start
where oper_gr = 12) s12 on gs.num = s12.mnem_gr
left join (select *
from lets_start
where oper_gr = 13) s13 on gs.num = s13.mnem_gr
left join (select *
from lets_start
where oper_gr = 14) s14 on gs.num = s14.mnem_gr
left join (select *
from lets_start
where oper_gr = 15) s15 on gs.num = s15.mnem_gr
left join (select * from balance where "date"='end') s16 on gs.num = s16.mnem_gr
-- left join (select *
-- from (select
-- acc_temp.group_num as mnem_gr,
-- sum(q.acc_type_value) total_cr
-- from acc_types_agg_in_group_temp acc_temp
-- join (select
-- q.direction, q.mnemonics, q.group_mnemonics, sum(q.acc_type_value) acc_type_value, 'start'
-- from temp_transactions q
-- where direction = 'credit'
-- group by q.direction, q.mnemonics, q.group_mnemonics) q on
-- case when acc_temp.group_type is null then true else acc_temp.group_type = q.group_mnemonics end
-- and case when acc_temp.type_in is null then true else acc_temp.type_in = q.mnemonics end
-- and case when acc_temp.type_out is null then true else q.mnemonics <> all(acc_temp.type_out::text[]) end
-- group by acc_temp.group_num)) s17 on gs.num = s17.mnem_gr
-- left join (select *
-- from (select
-- acc_temp.group_num as mnem_gr,
-- sum(q.acc_type_value) total_db
-- from acc_types_agg_in_group_temp acc_temp
-- join (select
-- q.direction, q.mnemonics, q.group_mnemonics, sum(q.acc_type_value) acc_type_value, 'start'
-- from temp_transactions q
-- where direction = 'debit'
-- group by q.direction, q.mnemonics, q.group_mnemonics) q on
-- case when acc_temp.group_type is null then true else acc_temp.group_type = q.group_mnemonics end
-- and case when acc_temp.type_in is null then true else acc_temp.type_in = q.mnemonics end
-- and case when acc_temp.type_out is null then true else q.mnemonics <> all(acc_temp.type_out::text[]) end
-- group by acc_temp.group_num)) s18 on gs.num = s18.mnem_gr
left join (select *
from lets_start
where oper_gr = 16) s19 on gs.num = s19.mnem_gr
left join (select *
from lets_start
where oper_gr = 17) s20 on gs.num = s20.mnem_gr
left join (select *
from lets_start
where oper_gr = 18) s21 on gs.num = s21.mnem_gr
order by num
) gen
;
raise notice 'end return query %', clock_timestamp();
end case;
drop table if exists selected_funds;
drop table if exists temp_transactions;
drop table if exists acc_types_agg_in_group_temp;
drop table if exists oper_types_agg_in_group_temp;
drop table if exists balance;
drop table if exists pg_temp.balance_relevant;
END;
$function$
