From: | Michal Szymanski <dyrex(at)poczta(dot)onet(dot)pl> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Partitioning table and dynamic SQL in trigger |
Date: | 2009-11-24 12:22:01 |
Message-ID: | 1f82903a-efe5-4a7a-9f97-cc680208b63a@f20g2000vbl.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Fetter | 2009-11-24 12:48:29 | Re: [BUGS] BUG #5211: invalid password |
Previous Message | Pavel Stehule | 2009-11-24 12:14:25 | Re: [COMMITTERS] recover deleted records |