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

[Haifeng Liu]

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!

[Craig Ringer]

Post by Haifeng Liu
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