PostgreSQL. Auto create partition tables trigger function.

gistfile1.sql

-- Function: cb_stat_insert_trigger_function()

-- DROP FUNCTION cb_stat_insert_trigger_function();

CREATE OR REPLACE FUNCTION cb_stat_insert_trigger_function()
  RETURNS trigger AS
$BODY$
DECLARE 
    dateName text;
    tableName text;
    fromTime text;
    fromTimeBegin timestamp with time zone;
    toTime timestamp without time zone;
    createStmt text;
BEGIN
    SELECT table_name INTO fromTime FROM information_schema.tables WHERE table_schema = 'public' AND table_name ILIKE 'cb_stat_%' ORDER BY table_name DESC LIMIT 1;
    fromTime := substring(fromTime, '\d+_\d+');
    fromTime := overlay(fromTime placing '' from 9 for 1);
    fromTimeBegin := to_timestamp(fromTime, 'YYYYMMDDHH24MISS');
    toTime := fromTimeBegin + interval '1 minute';
    -- newTime = to_char(NEW.timestamp,'YYYY-MM-DD HH24:MI:SS');
    dateName := to_char(NOW(),'YYYYMMDD_HH24MI'); 
    tableName := 'cb_stat' || '_' || dateName;
    IF ( NOW() >= fromTimeBegin AND NOW() < toTime ) THEN
        createStmt := 'INSERT INTO '||tableName||'(cb_id, bookings_count, timestamp) VALUES ('|| NEW.cb_id||','|| NEW.bookings_count||','''|| NOW()||''')';
        EXECUTE createStmt;
    ELSE
        createStmt := 'CREATE TABLE '||tableName||' () INHERITS (cb_stat)';
        EXECUTE createStmt;
        createStmt := 'ALTER TABLE '||tableName||' ADD PRIMARY KEY (timestamp)';
        EXECUTE createStmt;
        createStmt := 'INSERT INTO '||tableName||'(cb_id, bookings_count, timestamp) VALUES ('|| NEW.cb_id||','|| NEW.bookings_count||','''|| NOW()||''')';
        EXECUTE createStmt;
    END IF;
    RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;
  COST 100;

-- create trigger on main table
CREATE TRIGGER cb_stat_insert_trigger
  BEFORE INSERT
  ON cb_stat
  FOR EACH ROW
  EXECUTE PROCEDURE cb_stat_insert_trigger_function();