Search Postgresql Archives

partitioning and dynamic query creation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello list,

I'm currently thinking about a possibility to create a dynamic insert 
statement inside a trigger function to put the data in the correct partition.
What am I talking about ?
I want to put data dependant on a timestamp column ("datetime") in seperate 
partitions. Therefore I created the partitions, checks and a trigger function 
with the following code:
""
    ...
    date_part='';
    date_part = to_char(NEW.datetime,'YYYY') || to_char(NEW.datetime,'MM');
    tablename = 'table_' || date_part;
    RAISE NOTICE 'target table: %', tablename;
    EXECUTE 'insert into ' || tablename::regclass || ' values (NEW.*);';
    --IF ( DATE (NEW.datetime) >= DATE '2010-11-01' AND
    --     DATE (NEW.datetime) < DATE '2010-12-01' ) THEN
    --    INSERT INTO tab_tour201011 VALUES (NEW.*);
    --ELSIF ( DATE (NEW.datetime) >= DATE '2010-12-01' AND
    --        DATE (NEW.datetime) < DATE '2011-01-01' ) THEN
    --    INSERT INTO tab_tour201012 VALUES (NEW.*);
    --ELSIF ( DATE (NEW.datetime) >= DATE '2011-01-01' AND
    --        DATE (NEW.datetime) < DATE '2011-02-01' ) THEN
    --    INSERT INTO tab_tour201101 VALUES (NEW.*);
    --ELSIF ( DATE (NEW.datetime) >= DATE '2011-02-01' AND
    --        DATE (NEW.datetime) < DATE '2011-03-01' ) THEN
    --    INSERT INTO tab_tour201102 VALUES (NEW.*);
   ...
""

The above code throws the following error while trying to insert data:
""
NOTICE:  target table: table_201102
ERROR:  missing FROM-clause entry for table "new"
LINE 1: insert into table_201102 values (NEW.*);
                                           ^
QUERY:  insert into table_201102 values (NEW.*);
CONTEXT:  PL/pgSQL function "insert_trigger" line 10 at EXECUTE statement
""

O.K., most probably this is caused by the fact that the statement "string" 
includes the characters NEW, but not the values...or what....?!?!
The commented lines are working as expected and I think this is the common way 
of handling partitions.

Now my question:
is it possible at all to create the insert statement on the fly, to avoid 
modifying the trigger function each time a new partition has been added ?

any help appreciated....::GERD::....

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux