Example of a data partitioning by month function - Postgres

customer_transactions_insert_function_example.sql

CREATE OR REPLACE FUNCTION customer_transactions_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
  target_partitioned_by text;
  table_name text;
BEGIN
    -- get the month from the datetime transasction_date field
    SELECT cast(extract(month from NEW.transasction_date) AS TEXT) INTO target_partitioned_by;
    table_name = 'customer_transactions_y2020_m' || target_partitioned_by;

    -- insert into the appropriate partition if date within appropriate range
    IF (NEW.transasction_date >= '2020-01-01 00:00:00' AND NEW.transasction_date <= '2020-12-31 23:59:59' ) 
        THEN EXECUTE 'INSERT INTO ' || table_name || ' SELECT * FROM (SELECT $1.*) AS t' USING NEW;
    ELSE
        -- something went very wrong - unexpected data
        INSERT INTO customer_transactions_catch_all VALUES (NEW.*);
        RAISE NOTICE 'Date out of range. Inserting into the catch all partition...';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;