Thread: How to make the row changes inside trigger function visible to the top level sql statement?
How to make the row changes inside trigger function visible to the top level sql 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 test affectedrows is also used frequently. pgAdmin do test 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 the row changes inside trigger function visible to the top level sql statement?
From
Sergey Konoplev
Date:
On Mon, Aug 6, 2012 at 1:14 PM, haifeng liu <haifeng.813@gmail.com> 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 testaffected rows is also used frequently. pgAdmin do test 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? It is usually not necessary to check it separately because if there were no errors than the row has been inserted successfully assuming your redirecting function is okay. However the only way to get the inserted row back is to do it from a subsequent statement in the partitioning case. In your application you can use LISTEN/NOTIFY if you need to get some specific information from trigger BTW. May be it will help somehow. -- Sergey Konoplev a database architect, software developer at PostgreSQL-Consulting.com http://www.postgresql-consulting.com Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204
Re: How to make the row changes inside trigger function visible to the top level sql statement?
From
Sergey Konoplev
Date:
On Thu, Aug 9, 2012 at 2:53 PM, Sergey Konoplev <sergey.konoplev@postgresql-consulting.com> wrote: >> How can I make the row changes inside the trigger function visible to the top level statement? > > your redirecting function is okay. However the only way to get the > inserted row back is to do it from a subsequent statement in the > partitioning case. BTW I am not completely correct here. There is a solution but it is a tricky one https://gist.github.com/59067. I am not sure if it is a good performing one but it will work. > In your application you can use LISTEN/NOTIFY if you need to get some > specific information from trigger BTW. May be it will help somehow. > > -- > Sergey Konoplev > > a database architect, software developer at PostgreSQL-Consulting.com > http://www.postgresql-consulting.com > > Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204 -- Sergey Konoplev a database architect, software developer at PostgreSQL-Consulting.com http://www.postgresql-consulting.com Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204
Re: How to make the row changes inside trigger function visible to the top level sql statement?
From
Haifeng Liu
Date:
On Aug 9, 2012, at 7:11 PM, Sergey Konoplev <sergey.konoplev@postgresql-consulting.com> wrote: > On Thu, Aug 9, 2012 at 2:53 PM, Sergey Konoplev > <sergey.konoplev@postgresql-consulting.com> wrote: >>> How can I make the row changes inside the trigger function visible to the top level statement? >> >> your redirecting function is okay. However the only way to get the >> inserted row back is to do it from a subsequent statement in the >> partitioning case. > > BTW I am not completely correct here. There is a solution but it is a > tricky one https://gist.github.com/59067. I am not sure if it is a > good performing one but it will work. I don't think it's a good solution for a partitioned table. Any how, thank you for the information. > >> In your application you can use LISTEN/NOTIFY if you need to get some >> specific information from trigger BTW. May be it will help somehow. >> >> -- >> Sergey Konoplev >> >> a database architect, software developer at PostgreSQL-Consulting.com >> http://www.postgresql-consulting.com >> >> Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204 > > > > -- > Sergey Konoplev > > a database architect, software developer at PostgreSQL-Consulting.com > http://www.postgresql-consulting.com > > Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204 > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin >