Shulou(Shulou.com)05/31 Report--

This article will explain in detail how postgresql is divided into hourly tables. Xiaobian thinks it is quite practical, so share it with you for reference. I hope you can gain something after reading this article.

method

CREATE OR REPLACE FUNCTION auto_insert_into_tbl_partition() RETURNS trigger AS$BODY$DECLARE time_column_name text ; --Name of time field in parent table for partition [Must be initialized first!!] curMM varchar(16); -- 'YMM' string, used as suffix isExisting for partition subtables boolean; --partition subtable, whether strSQL already exists text; BEGIN --(time field name) must be initialized before invocation:time_column_name [get directly from invocation parameters!!] time_column_name := TG_ARGV[0]; --Determine whether the corresponding partition table already exists? EXECUTE 'SELECT $1. '||time_column_name INTO strSQL USING NEW; curMM := to_char( strSQL::timestamp , 'YYYYMMDDHH' ); select count(1) INTO isExist from pg_class where relname = ('t_audit_'||curMM); --If it doesn't exist, create subpartition IF (isExisting = false ) THEN before inserting --Create child partition table strSQL := 'CREATE TABLE IF NOT EXISTS t_audit_'||curMM||'() INHERITS (t_audit);' ; EXECUTE strSQL; --Create index strSQL := 'CREATE INDEX t_audit_'||curMM||'_INDEX_'||time_column_name||' ON t_audit_'||curMM||' ('||time_column_name||');' ; EXECUTE strSQL; END IF; --Insert data into child partitions! strSQL := 'INSERT INTO t_audit_'||curMM||' SELECT $1.* ' ; EXECUTE strSQL USING NEW; RETURN NULL; END$BODY$ LANGUAGE plpgsql;

I am according to the hours of a fixed table for sub-table, in fact, can write live is to add a variable, spell sql when splicing into. This is a callback function that writes a function as a trigger. Just judge whether this table has it first. If it has it, insert it directly. If not, build a table and insert it again.

CREATE TRIGGER insert_tbl_partition_trigger BEFORE INSERT ON t_audit FOR EACH ROW EXECUTE PROCEDURE auto_insert_into_tbl_partition('time');

Create a trigger that executes the previous callback function when inserting data into the parent table.

ps: here's a look at the postgresql table trigger

1. First create a function to execute the script to be executed after the trigger is started.

CREATE OR REPLACE FUNCTION "public". "trigger_day_aqi"() RETURNS "pg_catalog". "trigger" AS $BODY$BEGIN --daily average table, no o3, hourly value table, no o3_8h NEW.so2iaqi=DAY_SO2_AQI(NEW.so2); NEW.no2iaqi=DAY_NO2_AQI(NEW.no2); NEW.coiaqi=DAY_CO_AQI(NEW.co); NEW.o3_8hiaqi=O3_8_AQI(NEW.o3_8h); NEW.pm10iaqi=PM10_AQI(NEW.pm10); NEW.pm25iaqi=PM25_AQI(NEW.pm25); NEW.aqi=NEW.coiaqi; NEW.primarypol='CO'; IF NEW.aqi

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.