Thread: 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 ?
From
Haifeng Liu
Date:
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!
Re: How to make data changes inside trigger function visible to the top level statement ?
From
Craig Ringer
Date:
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