-- 创建触发器函数
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()
