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()