Autopartitioning Postgresql for Zabbix

Question

The goal, аutopartitioning for 7 days. And after 14 days to delete the old partitions. In this example, everything works. But, when I try to write data of the form :

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

I get an error

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

Here is the actual code example

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;

I found out that the problem when writing the values of being in NEW.value.And after replacing the characters [(), \] with _, the problem was solved.

That is, I redefine before an insert NEW.value

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

But this is the case if I try to write to a table with a column value, and if there is no other table, I have to write many identical functions for each table. What is bad. Can you know why this situation arises with these symbols? PostgreSQL 9.5.9

Answer 1

You could try USING and expand row with asterisk:

var_data := 'INSERT INTO ' || partition_name || ' values ($1.*);';
RAISE INFO 'SQL: %',var_data;
EXECUTE var_data using new;