Самоуправляемые таблицы разделов PostgreSQL

Вы смешиваете double precision вывод date_part() с text '-'. Это не имеет смысла для PostgreSQL. Вам потребуется явное приведение к text. Но есть гораздо более простой способ сделать все это:

startdate:=date_part('year',to_timestamp(NEW.date))
||'-'||date_part('month',to_timestamp(NEW.date))
||'-'||date_part('day',to_timestamp(NEW.date));

Вместо этого используйте:

startdate := to_char(NEW.date, 'YYYY-MM-DD');

Это тоже не имеет смысла:

EXECUTE 'CREATE TABLE $1 (
        CHECK (date >= DATE $2 AND date < DATE $3 )
    ) INHERITS (pings)' USING quote_ident(tablename),startdate,enddate;

Вы можете предоставлять значения только с предложением USING. Читайте руководство здесь . Вместо этого попробуйте:

EXECUTE 'CREATE TABLE ' || quote_ident(tablename) || ' (
            CHECK ("date" >= ''' || startdate || ''' AND
                   "date" <  ''' || enddate   || '''))
            INHERITS (ping)';

Или, что еще лучше, используйте format(). . Смотри ниже.

Кроме того, например, @a_horse ответил: вам нужно поместить текстовые значения в одинарные кавычки.

Аналогично здесь:

EXECUTE 'INSERT INTO $1 VALUES (NEW.*)' USING quote_ident(tablename);

Вместо:

EXECUTE 'INSERT INTO ' || quote_ident(tablename) || ' VALUES ($1.*)'
USING NEW;

Связанный ответ:

• Как динамически использовать TG_TABLE_NAME в PostgreSQL 8.2?

В стороне: хотя «дата» разрешена для имени столбца в PostgreSQL, это зарезервированное слово в каждом стандарте SQL. Не называйте свой столбец «дата», это приводит к путанице синтаксических ошибок.

Полная рабочая демонстрация

CREATE TABLE ping (ping_id integer, the_date date);

CREATE OR REPLACE FUNCTION trg_ping_partition()
  RETURNS trigger AS
$func$
DECLARE
   _tbl text := to_char(NEW.the_date, '"ping_"YYYY_DDD_') || NEW.ping_id;
BEGIN
   IF NOT EXISTS (
      SELECT 1
      FROM   pg_catalog.pg_class c
      JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
      WHERE  n.nspname = 'public'  -- your schema
      AND    c.relname = _tbl
      AND    c.relkind = 'r') THEN

      EXECUTE format('CREATE TABLE %I (CHECK (the_date >= %L AND
                                              the_date <  %L)) INHERITS (ping)'
              , _tbl
              , to_char(NEW.the_date,     'YYYY-MM-DD')
              , to_char(NEW.the_date + 1, 'YYYY-MM-DD')
              );
   END IF;

   EXECUTE 'INSERT INTO ' || quote_ident(_tbl) || ' VALUES ($1.*)'
   USING NEW; 

   RETURN NULL;
END
$func$ LANGUAGE plpgsql SET search_path = public;

CREATE TRIGGER insbef
BEFORE INSERT ON ping
FOR EACH ROW EXECUTE PROCEDURE trg_ping_partition();

• Обновление. В более поздних версиях Postgres есть более элегантные способы проверки существования таблицы:

  • How to check if a table exists in a given schema

• to_char() может принять date за $1. Оно автоматически конвертируется в timestamp.
  Руководство по функциям даты/времени .

• (Необязательно) SET search_path для объема вашей функции, чтобы избежать неправомерных действий с измененным параметром search_path.

• Множество других упрощений и улучшений. Сравните код.

Тесты:

INSERT INTO ping VALUES (1, now()::date);
INSERT INTO ping VALUES (2, now()::date);
INSERT INTO ping VALUES (2, now()::date + 1);
INSERT INTO ping VALUES (2, now()::date + 1);

Скрипт SQL.

Динамическое секционирование в PostgreSQL — просто плохая идея. Ваш код небезопасен в многопользовательской среде. Чтобы это было безопасно, вам придется использовать блокировки, которые замедляют выполнение. Оптимальное количество разделов около ста. Вы можете легко создать это количество заблаговременно, чтобы значительно упростить логику, необходимую для разделения.

Вам нужно поместить литералы даты в одинарные кавычки. В настоящее время вы выполняете что-то вроде этого:

 CHECK ( date >= DATE 2011-10-25 AND date < DATE 2011-11-25 )

что недействительно. В этом случае 2011-10-25 интерпретируется как 2011 минус 10 минус 25

Ваш код должен создать SQL, используя одинарные кавычки вокруг литерала даты:

CHECK ( date >= DATE '2011-10-25' AND date < DATE '2011-11-25' )

Я разобрался со всем, и это прекрасно работает, даже есть автоматическое удаление через 30 дней. Я надеюсь, что это поможет будущим людям, которые ищут функцию триггера авторазбиения.

CREATE FUNCTION ping_partition() RETURNS trigger
    LANGUAGE plpgsql
    AS $_$
DECLARE
_keepdate text;
_tablename text;
_startdate text;
_enddate text;
_result record;
BEGIN
_keepdate:=to_char(to_timestamp(NEW.date) - interval '30 days', 'YYYY-MM-DD');
_startdate := to_char(to_timestamp(NEW.date), 'YYYY-MM-DD');
_tablename:='pings_'||NEW.id||'_'||_startdate;
PERFORM 1
FROM   pg_catalog.pg_class c
JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relkind = 'r'
AND    c.relname = _tablename
AND    n.nspname = 'pinglog';
IF NOT FOUND THEN
    _enddate:=_startdate::timestamp + INTERVAL '1 day';
    EXECUTE 'CREATE TABLE pinglog.' || quote_ident(_tablename) || ' (
        CHECK ( date >= EXTRACT(EPOCH FROM DATE ' || quote_literal(_startdate) || ')
            AND date < EXTRACT(EPOCH FROM DATE ' || quote_literal(_enddate) || ')
            AND id = ' || quote_literal(NEW.id) || '
        )
    ) INHERITS (pinglog.pings)';
    EXECUTE 'CREATE INDEX ' || quote_ident(_tablename||'_indx1') || ' ON pinglog.' || quote_ident(_tablename) || ' USING btree (microseconds) WHERE microseconds IS NULL';
    EXECUTE 'CREATE INDEX ' || quote_ident(_tablename||'_indx2') || ' ON pinglog.' || quote_ident(_tablename) || ' USING btree (date, id)';
    EXECUTE 'CREATE INDEX ' || quote_ident(_tablename||'_indx3') || ' ON pinglog.' || quote_ident(_tablename) || ' USING btree (date, id, microseconds) WHERE microseconds IS NULL';
END IF;
EXECUTE 'INSERT INTO ' || quote_ident(_tablename) || ' VALUES ($1.*)' USING NEW;
FOR _result IN SELECT * FROM pg_tables WHERE schemaname='pinglog' LOOP
    IF char_length(substring(_result.tablename from '[0-9-]*$')) <> 0 AND (to_timestamp(NEW.date) - interval '30 days') > to_timestamp(substring(_result.tablename from '[0-9-]*$'),'YYYY-MM-DD') THEN
        -- RAISE EXCEPTION 'timestamp=%,table=%,found=%',to_timestamp(substring(_result.tablename from '[0-9-]*$'),'YYYY-MM-DD'),_result.tablename,char_length(substring(_result.tablename from '[0-9-]*$'));
        -- could have it check for non-existant ids as well, or for archive bit and only delete if the archive bit is not set
        EXECUTE 'DROP TABLE ' || quote_ident(_result.tablename);
    END IF;
END LOOP;
RETURN NULL;
END;
$_$;