Partitioning table and dynamic SQL in trigger
От | Michal Szymanski |
---|---|
Тема | Partitioning table and dynamic SQL in trigger |
Дата | |
Msg-id | 1f82903a-efe5-4a7a-9f97-cc680208b63a@f20g2000vbl.googlegroups.com обсуждение исходный текст |
Ответы |
Re: Partitioning table and dynamic SQL in trigger
|
Список | 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
В списке pgsql-general по дате отправления: