trigger.sql

application/sql

Download

create or replace procedure rebuild_rule_set_selector(
) as
$body$
declare
    l_code  text:= E'create or replace function public.rule_set_selector(
    in i_attribute_values jsonb
,   in i_stage_id         integer
) returns setof public.rul_rule_set as
\$generated_body\$
-- :last_modification:
with parameters as (
    select
        :parameters:
)
select rrs.*
from  rul_rule_set rrs
cross join parameters
where true
    and ( rrs.stage_id = i_stage_id or rrs.stage_id is null )
    and (
:conditions:
         )
\$generated_body\$ language sql;';
    l_parameters    text;
    l_conditions    text;
begin
    l_code:= replace(l_code, ':last_modification:', 'Последнее изменение: '||clock_timestamp()::text);
 
    select string_agg(format(E'(i_attribute_values ->> \'%1$s\')::%2$s as "%1$s" -- %3$s', s.attr_name, s.attr_type, s.attr_desc), E'\n    ,   ')
    into l_parameters
    from (select * from public.doc_attribute s order by s.id) as s
    ;
    l_code:= replace(l_code, ':parameters:', coalesce(l_parameters, 'null as nothing'));
 
    select string_agg(format(E'(rrs.id = %1$s and (%2$s)) -- %3$s', s.id, s.condition_compiled, s.description), E'\n   or ')
    into l_conditions
    from (select rrs.id, rrs.condition_compiled, rrs.description from public.rul_rule_set rrs order by rrs.id) as s
    ;
    l_code:= replace(l_code, ':conditions:', coalesce(l_conditions, 'true'));
 
    raise notice 'l_code = %', l_code::text;
    execute l_code;
 
    perform rule_set_selector(null, null);
end
$body$ language plpgsql;


create or replace function rul_rule_set_trg(
) returns trigger as
$body$
declare
begin
    call public.rebuild_rule_set_selector();
    return new;
end
$body$ language plpgsql;

create trigger rul_rule_set_aiodt_er_trgr  after insert or update or delete on rul_rule_set for each row execute procedure rul_rule_set_trg ();

alter table rul_rule_set enable replica trigger rul_rule_set_aiodt_er_trgr;