Partitioning table and dynamic SQL in trigger

[Michal Szymanski]

In our DB we create partition table for each month (our naming
convetion is follow , and I would like to create trigger that insert
data to appropriate partition table accounting.cdr_y2009m05,
accounting.cdr_y2009m06 etc..).

What is the best solution to create such trigger? I can create trigger
function as follow:

CREATE OR REPLACE FUNCTION partitionig_test RETURNS TRIGGER AS $$
BEGIN
IF NEW.effective_date>=DATE '2009-05-01' and
NEW.effective_date<DATE '2009-06-01' THEN
INTO accounting.cdr_y2009m05 VALUES (NEW.*);
ELSE IF ....
...................................

but in such solution every month I have modify trigger to handle new
month (during import I have to remember about archive months). Second
solution is to create dynamic SQL query, but every call of
partitionig_test I have to build long string - accounting.cdr_* table
has 50 columns.
Maybe you know third solution, most elegant ?

Michal Szymanski
http://blog.szymanskich.net

[silly8888]

How about:

IF date_trunc('month',NEW.effective_date)=date_trunc('month',now()) THEN
.....

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

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