bartek pisze

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.