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

Add your comment!