Обсуждение: How to make data changes inside trigger function visible to the top level statement ?

Hello,

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

Partition table using a trigger function to redirect insertion to the correct partition is the recommend way, and
affectedrows testing is also used widely. pgAdmin tests the affected rows too, thus when I type a new row and click
savebutton, 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!

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
tothe top level statement. 
>
> Partition table using a trigger function to redirect insertion to the correct partition is the recommend way, and
affectedrows testing is also used widely. pgAdmin tests the affected rows too, thus when I type a new row and click
savebutton, 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