repcmd.sql

application/sql

Download

CREATE TABLE public.doc_attribute (id integer NOT NULL, attr_name text, attr_desc text, attr_type text, attr_order integer, is_system boolean, used_by_slice boolean);
INSERT INTO doc_attribute VALUES ('1','name','Имя','text','1','t','t');
CREATE TABLE public.rul_rule_set (id smallint NOT NULL, description text, stage_id integer, condition text, condition_compiled text);
ALTER TABLE ONLY public.doc_attribute ADD CONSTRAINT doc_attribute_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.rul_rule_set ADD CONSTRAINT rul_rule_set_pkey PRIMARY KEY (id);


create or replace procedure public.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 as 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 public.rule_set_selector(null::jsonb, null::integer);
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;

CREATE SUBSCRIPTION test_sub CONNECTION 'port=XXXX user=postgres dbname=postgres' PUBLICATION test_pub;