Search Postgresql Archives

Re: Partitioning table and dynamic SQL in trigger

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

 



How about:

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



On Tue, Nov 24, 2009 at 7:22 AM, Michal Szymanski <dyrex@xxxxxxxxxxxxxx> wrote:
> 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
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
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