Gist Blog

Helper functions for timestamp partitioned table creation

This code is untested. The goal here is to make SQL functions that will create partition tables with just the table name and a number of months. This makes adding partitions via a scheduled job very simple.

CREATE OR REPLACE FUNCTION createMonthlyTablePartition(
    p_table_name text,
    p_month integer,
    p_year integer
) RETURNS text AS $$
DECLARE
    partition_name text;
    start_date date;
    end_date date;
BEGIN
    partition_name := regexp_replace(p_table_name, '\.', '__') || '__' || p_year || '_' || LPAD(p_month::text, 2, '0');
    start_date := DATE_TRUNC('month', DATE(p_year || '-' || LPAD(p_month::text, 2, '0') || '-01'));
    end_date := start_date + INTERVAL '1 month';

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.tables
        WHERE table_name = partition_name
        AND table_schema = current_schema()
    ) THEN
        EXECUTE format('CREATE TABLE %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)', partition_name, p_table_name, start_date, end_date);
        RETURN partition_name;
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION createMonthlyTablePartitions(
    p_table_name text,
    p_num_months integer
) RETURNS TABLE (partition_name text) AS $$
DECLARE
    current_date date := CURRENT_DATE + INTERVAL '1 month';
    i integer;
BEGIN
    FOR i IN 1..p_num_months LOOP
        partition_name := createMonthlyTablePartition(p_table_name, EXTRACT(MONTH FROM current_date)::integer, EXTRACT(YEAR FROM current_date)::integer);
        IF partition_name IS NOT NULL THEN
            RETURN NEXT;
        END IF;
        current_date := current_date + INTERVAL '1 month';
    END LOOP;

    RETURN;
END;
$$ LANGUAGE plpgsql;
59 23 28-31 * * if [ $(date +\%d -d tomorrow) -eq 1 ]; then psql -U your_username -d your_database_name -c "SELECT * FROM createMonthlyTablePartitions('public.your_table_name', 1);" > /dev/null; fi

Comments

To make a comment, please visit this posts Gist.

Add your comment!