PostgreSQL 10 触发器创建分区表以和索引

函数
date_time为分区字段

CREATE OR REPLACE FUNCTION "public"."insert_trigger_table"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
DECLARE date_timeflag TEXT;
DECLARE str_sql TEXT;
BEGIN
	date_timeflag := NEW.date_time;
	str_sql := 'INSERT INTO '||TG_RELNAME||'_'|| date_timeflag||' VALUES ($1.*)';
	EXECUTE str_sql USING NEW;
	RETURN NULL;
	EXCEPTION
	WHEN UNDEFINED_TABLE
	THEN
		EXECUTE
			'CREATE TABLE IF NOT EXISTS '||TG_RELNAME||'_'
			|| date_timeflag
			|| '(CHECK (date_time = '''
			|| date_timeflag
			|| ''')) INHERITS ('||TG_RELNAME||');'
	
	'CREATE INDEX "prplisrepairlist_city_2_' || date_timeflag ||'" ON ' || TG_RELNAME||'_'|| date_timeflag || ' USING btree (date_time);

CREATE INDEX "prplisrepairlist_county_3_' || date_timeflag ||'" ON ' || TG_RELNAME||'_' || date_timeflag || ' USING btree (date_time)';
			
		EXECUTE str_sql USING NEW;
    RETURN NULL;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

ALTER FUNCTION "public"."insert_trigger_table"() OWNER TO "postgres";

触发器
创建触发器

CREATE TRIGGER "insert_trigger" BEFORE INSERT ON "public"."test"
FOR EACH ROW 
EXCUTE PROCEDURE "public"."insert_trigger_table"();

删除触发器

DROP TRIGGER "insert_trigger" on "public"."test";

这篇好文章是转载于：学新通技术网