PostgreSQL 10 触发器创建分区表以及索引_pg10 partition by 自动分区触发器-CSDN博客

本文链接：https://blog.csdn.net/SINGLEP/article/details/124345225

本文介绍了如何在PostgreSQL 10中利用触发器创建分区表，并探讨了相关索引的建立方法，重点在于date_time字段的分区策略。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

函数
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";