Creating PostgreSQL table partitions automatically based on the date field (type date as well) -- each day is a single partition

partition.sql

CREATE TABLE testing_partition(patent_id BIGINT, date DATE) WITH ( OIDS=FALSE);

CREATE OR REPLACE FUNCTION create_partition_and_insert() RETURNS trigger AS
  $BODY$
    DECLARE
      partition_date TEXT;
      partition TEXT;
    BEGIN
      partition_date := to_char(NEW.date,'YYYY_MM_DD');
      partition := TG_TABLE_NAME || '_' || partition_date;
      IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
        RAISE NOTICE 'A partition has been created %',partition;
        EXECUTE 'CREATE TABLE ' || partition || ' (check (date = ''' || NEW.date || ''')) INHERITS (' || TG_TABLE_NAME || ');';
      END IF;
      EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').*;';
      RETURN NULL;
    END;
  $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE TRIGGER testing_partition_insert_trigger
BEFORE INSERT ON testing_partition
FOR EACH ROW EXECUTE PROCEDURE create_partition_and_insert();

This is very helpful. Looks like TG_RELNAME is deprecated, and TG_TABLE_NAME should be used instead.

This is very helpful. Looks like TG_RELNAME is deprecated, and TG_TABLE_NAME should be used instead.

Thanks! I am going to update the script.