PG触发器创建/函数创建

-- 创建触发器函数
CREATE OR REPLACE FUNCTION push_insert_update_trigger_fun()
returns trigger as $$
begin
        IF ((
    SELECT count(1) FROM public.test1
        WHERE
        catalog_title = new.catalog_title  and  xxa_sjgl=new.xxa_sjgl and
        xxb_sjgl=new.xxb_sjgl and xxc_sjgl=new.xxc_sjgl and create_time = new.create_time AND update_time = new.update_time
        ) = 0) 
        THEN
        INSERT INTO public.test1
        (trade_type, catalog_title, name, xxa_sjgl, xxb_sjgl, xxc_sjgl, row_count, create_time, fxpc_pch_sjgl, push_shuhui, cause, update_time, conversion, pre_mark, catalog_level_type, pre_sheng, pre_shi, pre_xian) 
        VALUES
        (new.trade_type, new.catalog_title, new.name, new.xxa_sjgl, new.xxb_sjgl, new.xxc_sjgl, new.row_count, new.create_time, new.fxpc_pch_sjgl, new.push_shuhui, new.cause, new.update_time, NULL, 0, NULL, 0, 0, 0);
        ELSE RETURN NULL; END IF;
    return NULL;
end;
$$
language plpgsql;
 
 
-- 删除函数
DROP FUNCTION push_insert_update_trigger_fun;
-- 创建触发器
CREATE TRIGGER push_table_trigger_insert AFTER INSERT ON test FOR EACH ROW EXECUTE PROCEDURE push_insert_update_trigger_fun();
CREATE TRIGGER push_table_trigger_update AFTER UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE push_insert_update_trigger_fun();
-- 查看触发器
select * from information_schema.triggers
-- 删除触发器
DROP TRIGGER push_table_trigger_insert ON test

--创建物化视图刷新方法
create or replace function refresh_materialized()
returns varchar as $$
begin
 -- 写上需要刷新的物化视图
 refresh materialized view riskzoning.test;
 refresh materialized view riskzoning.test;
 return 'success';
end; $$
language plpgsql;
 
-- 执行方法刷新
select refresh_materialized()