Partycjnowanie tablicy w PostgreSQL
19 lipiec, 2011
Przykład funkcji wyzwalanej przy dodawaniu wiersza do partycjonowanej tabeli.
CREATE OR REPLACE FUNCTION log_insert_trigger() RETURNS TRIGGER AS $$ DECLARE l_year double precision; DECLARE l_month double precision; DECLARE l_year_next double precision; DECLARE l_month_next double precision; DECLARE s_month text; DECLARE s_table text; DECLARE i_tbl_count int; BEGIN l_year:=(EXTRACT(YEAR FROM NEW."downloadDate")); l_month:=(EXTRACT(MONTH FROM NEW."downloadDate")); l_year_next:=(EXTRACT(YEAR FROM (NEW."downloadDate" + interval '1 month'))); l_month_next:=(EXTRACT(MONTH FROM (NEW."downloadDate" + interval '1 month'))); IF (l_month < 10) THEN s_month := '0' || l_month; ELSE s_month := l_month; END IF; s_table := 'log_y' || l_year || 'm' || s_month; SELECT count(*) INTO i_tbl_count FROM pg_tables WHERE tablename = s_table AND schemaname = 'lps'; IF (i_tbl_count < 1) THEN EXECUTE 'CREATE TABLE lps.' || s_table || ' (' || 'CHECK ( "downloadDate" >= DATE ''' || l_year || '-' || l_month || '-01'' ' || 'AND "downloadDate" < DATE ''' || l_year_next || '-' || l_month_next || '-01'' )' || ') INHERITS (lps.log);'; END IF; EXECUTE 'INSERT INTO lps.' || s_table || ' VALUES ($1.*)' USING NEW; RETURN NULL; END; $$ LANGUAGE plpgsql;
Funkcja jest bezobsługowa, nie wymaga tworzenia tabel na zapas i modyfikacji funkcji każdego miesiąca.