title: Self-managing PostgreSQL partition tables
body: <p>I am trying to make a self-managing partition table setup with Postgres. It all revolves around this function but I can't seem to get Postgres to accept my table names. Any ideas or examples of self-managing partition table trigger functions?</p>
  
  <p>My current function:</p>
  
  <pre><code>DECLARE
  day integer;
  year integer;
  tablename text;
  startdate text;
  enddate text;
  BEGIN
  day:=date_part('doy',to_timestamp(NEW.date));
  year:=date_part('year',to_timestamp(NEW.date));
  tablename:='pings_'||year||'_'||day||'_'||NEW.id;
  -- RAISE EXCEPTION 'tablename=%',tablename;
  PERFORM 'tablename' FROM pg_tables WHERE 'schemaname'=tablename;
  -- RAISE EXCEPTION 'found=%',FOUND;
  IF FOUND &lt;&gt; TRUE THEN
      startdate:=date_part('year',to_timestamp(NEW.date))||'-'||date_part('month',to_timestamp(NEW.date))||'-'||date_part('day',to_timestamp(NEW.date));
      enddate:=startdate::timestamp + INTERVAL '1 day';
      EXECUTE 'CREATE TABLE $1 (
          CHECK ( date &gt;= DATE $2 AND date &lt; DATE $3 )
      ) INHERITS (pings)' USING quote_ident(tablename),startdate,enddate;
  END IF;
  EXECUTE 'INSERT INTO $1 VALUES (NEW.*)' USING quote_ident(tablename);
  RETURN NULL;
  END;
  </code></pre>
  
  <p>I want it to auto-create a table called <code>pings_YEAR_DOY_ID</code> but it always fails with:</p>
  
  <pre><code>2011-10-24 13:39:04 CDT [15804]: [1-1] ERROR:  invalid input syntax for type double precision: "-" at character 45
  2011-10-24 13:39:04 CDT [15804]: [2-1] QUERY:  SELECT date_part('year',to_timestamp( $1 ))+'-'+date_part('month',to_timestamp( $2 ))+'-'+date_part('day',to_timestamp( $3 ))
  2011-10-24 13:39:04 CDT [15804]: [3-1] CONTEXT:  PL/pgSQL function "ping_partition" line 15 at assignment
  2011-10-24 13:39:04 CDT [15804]: [4-1] STATEMENT:  INSERT INTO pings VALUES (0,0,5);
  </code></pre>
  
  <h3>TRY 2</h3>
  
  <p>After applying the changes and modifying it some more (date is a unixtimestamp column, my thinking being that an integer column is faster than a timestamp column when selecting). I get the below error, not sure if I am using the proper syntax for <code>USING NEW</code>?</p>
  
  <p>Updated function:</p>
  
  <pre><code>CREATE FUNCTION ping_partition() RETURNS trigger
      LANGUAGE plpgsql
      AS $_$DECLARE
  day integer;
  year integer;
  tablename text;
  startdate text;
  enddate text;
  BEGIN
  day:=date_part('doy',to_timestamp(NEW.date));
  year:=date_part('year',to_timestamp(NEW.date));
  tablename:='pings_'||year||'_'||day||'_'||NEW.id;
  -- RAISE EXCEPTION 'tablename=%',tablename;
  PERFORM 'tablename' FROM pg_tables WHERE 'schemaname'=tablename;
  -- RAISE EXCEPTION 'found=%',FOUND;
  IF FOUND &lt;&gt; TRUE THEN
      startdate := to_char(to_timestamp(NEW.date), 'YYYY-MM-DD');
      enddate:=startdate::timestamp + INTERVAL '1 day';
      EXECUTE 'CREATE TABLE ' || quote_ident(tablename) || ' (
          CHECK ( date &gt;= EXTRACT(EPOCH FROM DATE ' || quote_literal(startdate) || ')
              AND date &lt; EXTRACT(EPOCH FROM DATE ' || quote_literal(enddate) || ') )
      ) INHERITS (pings)';
  END IF;
  EXECUTE 'INSERT INTO ' || quote_ident(tablename) || ' SELECT $1' USING NEW; 
  RETURN NULL;
  END;
  $_$;
  </code></pre>
  
  <p>My statement:</p>
  
  <pre><code>INSERT INTO pings VALUES (0,0,5);
  </code></pre>
  
  <p>SQL error:</p>
  
  <blockquote>
  <pre><code>ERROR:  column "date" is of type integer but expression is of type pings
  LINE 1: INSERT INTO pings_1969_365_0 SELECT $1
                                              ^
  HINT:  You will need to rewrite or cast the expression.
  QUERY:  INSERT INTO pings_1969_365_0 SELECT $1
  CONTEXT:  PL/pgSQL function "ping_partition" line 22 at EXECUTE statement
  </code></pre>
  </blockquote>