针对Zabbix的Postgresql自动分区

目标是“自动分区7天”。并在14天后删除旧分区。在本例中,一切正常。但是,当我尝试编写表单数据时:

insert into history_str (itemid, clock, ns, value) values (40,151,3722, '3.0.3');

我出错了

ERROR: syntax error at or near ".3"
LINE 1: ... istory_str_2018_02_07 values (40,151,3.0.3,3722 ...
                                                    ^
QUERY: INSERT INTO history_str_2018_02_07 values (40,151,3.0.3,3722);
CONTEXT: PL / pgSQL function create_partition_other () line 37 at EXECUTE

下面是实际的代码示例

CREATE OR REPLACE FUNCTION create_partition() RETURNS trigger AS
  $BODY$
    DECLARE
      partition_name TEXT;
      partition_week TEXT;
      partitions_names TEXT;
      date_search TEXT;
      sql_search TEXT;
      var_data TEXT;
      typeof BOOL;

    BEGIN
      partition_week := to_char(to_timestamp(NEW.clock),'IW');
      RAISE INFO 'Week now: %',partition_week;
      partition_name := TG_TABLE_NAME || '_' || to_char(to_timestamp(NEW.clock),'YYYY_MM') || '_' || partition_week;
      RAISE INFO 'Master Table: %',TG_TABLE_NAME;
      RAISE INFO 'Partit. name: %',partition_name;

      IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname = partition_name) THEN
        RAISE INFO 'Create table';
        EXECUTE 'CREATE TABLE ' || partition_name || ' (check (clock >= ' || quote_literal(NEW.clock) || ' AND clock < ' || quote_literal(NEW.clock + integer '7' * integer '86400') || ')) INHERITS (' || TG_TABLE_NAME || ');';
        EXECUTE 'INSERT INTO create_tables_date (name,date) values (' || quote_literal(partition_name) || ',' || quote_literal(to_timestamp(NEW.clock)) || ');';
        date_search := quote_literal(date (to_char(to_timestamp(NEW.clock),'YYYY_MM_DD'))-integer '7');
        RAISE INFO 'Search data: %',date_search;
        sql_search := 'SELECT name FROM create_tables_date WHERE date < ' || date_search || ';';
        for partitions_names in EXECUTE sql_search LOOP
          IF partitions_names IS NOT NULL THEN
            RAISE INFO 'DROP, DELETE: %',partitions_names;
            EXECUTE 'DROP TABLE ' || partitions_names || ';';
            EXECUTE 'DELETE FROM create_tables_date WHERE name=' || quote_literal(partitions_names) || ';';
          END IF;
        END LOOP;
      END IF;
      RAISE INFO 'Value: %',NEW.value;

      var_data := 'INSERT INTO ' || partition_name || ' values ' || NEW || ';';
      RAISE INFO 'SQL: %',var_data;
      EXECUTE var_data;
      RETURN NULL;
    END;
  $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

我在写新的价值观时发现了这个问题。价值替换字符后 [(), \] 具有 _ ,问题解决了。

也就是说,我在插入新之前重新定义。价值

NEW.value := quote_literal(regexp_replace(NEW.value,'[(),\ ]','_','g'));

但如果我试图写入一个具有列值的表,如果没有其他表,则必须为每个表编写许多相同的函数,情况就是这样。什么是坏的。 你知道为什么这些符号会出现这种情况吗? PostgreSQL 9.5.9