Script to create partitionned tables and rotate it on one year
DECLARE +
row record; +
table_name text; +
sch_table_name text; +
table_not_exist text; +
sch_target text := 'partition'; +
rfid_user text := '<nomschema>'; +
partition_limit integer := 12; +
BEGIN +
-- get table name +
table_name := TG_TABLE_NAME || to_char(NEW.collection_date,'_YYYY_MM'); +
sch_table_name := sch_target || '.' || table_name; +
+
EXECUTE 'SELECT NOT EXISTS (SELECT FROM pg_tables ' +
|| 'WHERE schemaname = $1' +
|| 'AND tablename = $2)' +
INTO table_not_exist +
USING sch_target, table_name; +
+
-- If the partition table needed does not yet exist, then we create it: +
IF table_not_exist THEN +
+
-- Catch potential already exists exception and continue +
BEGIN +
EXECUTE format('CREATE TABLE %s(CONSTRAINT pk_%s PRIMARY KEY(RAW_HEX,COLLECTION_DATE,READER_ID))INHERITS(%s)',+
sch_table_name,table_name,TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME); +
FOR row IN +
EXECUTE 'SELECT child FROM partition.show_partitions order by child DESC OFFSET ' || partition_limit +
LOOP +
EXECUTE 'DROP TABLE ' || quote_ident('partition') || '.' || quote_ident(row.child); +
RAISE INFO 'Dropped table: %', quote_ident('partition') || '.' || quote_ident(row.child); +
END LOOP; +
EXCEPTION +
WHEN sqlstate '42P07' THEN +
NULL; +
WHEN OTHERS THEN +
RAISE; +
END; +
END IF; +
+
-- Insert the current record into the correct partition, which we are sure will now exist. +
EXECUTE 'INSERT INTO ' || sch_table_name || ' VALUES ($1.*)' USING NEW; +
RETURN NULL; +
END;
Laisser un commentaire