How to make data changes inside trigger function visible to the top level statement ?

Поиск
Список
Период
Сортировка
Искать

How to make data changes inside trigger function visible to the top level statement ?

От:
Haifeng Liu <liuhaifeng@live.com>
Дата:
Hello,

I am doing table partitioning, all is ok except that after executing 'insert' sql statement I can't get affected rows, it always be 0. After searching on the documents, I found that row changes inside trigger function is not visible to the top level statement.

Partition table using a trigger function to redirect insertion to the correct partition is the recommend way, and affected rows testing is also used widely. pgAdmin tests the affected rows too, thus when I type a new row and click save button, it seems failed but actually succeed.

How can I make the row changes inside the trigger function visible to the top level statement?

Thank all in advance!

Re: How to make data changes inside trigger function visible to the top level statement ?

От:
Craig Ringer <ringerc@ringerc.id.au>
Дата:
On 08/06/2012 06:50 PM, Haifeng Liu wrote:
> Hello,
>
> I am doing table partitioning, all is ok except that after executing 'insert' sql statement I can't get affected rows, it always be 0. After searching on the documents, I found that row changes inside trigger function is not visible to the top level statement.
>
> Partition table using a trigger function to redirect insertion to the correct partition is the recommend way, and affected rows testing is also used widely. pgAdmin tests the affected rows too, thus when I type a new row and click save button, it seems failed but actually succeed.
>
> How can I make the row changes inside the trigger function visible to the top level statement?
You can't. If you need an affected row count, you need to insert 
directly into the target partition, thus bypassing the trigger.

It's one of those things on the "wouldn't it be nice to fix" list in 
PostgreSQL's partitioning support.

--
Craig Ringer
FAQ