Skip site navigation (1) Skip section navigation (2)

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

From: Sergey Konoplev <sergey(dot)konoplev(at)postgresql-consulting(dot)com>
To: haifeng liu <haifeng(dot)813(at)gmail(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: How to make the row changes inside trigger function visible to the top level sql statement?
Date: 2012-08-09 10:53:54
Message-ID: CAL_0b1sbSV_4EEimLx2oVCSrsE7M5geQ3AJw6SOX5t+xEyidRg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
On Mon, Aug 6, 2012 at 1:14 PM, haifeng liu <haifeng(dot)813(at)gmail(dot)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 to the top level statement.
>
> Partition table using a trigger function to redirect insertion to the correct partition is the recommend way, and test affected rows 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?

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(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +79160686204

In response to

Responses

pgsql-admin by date

Next:From: Sergey KonoplevDate: 2012-08-09 11:11:45
Subject: Re: How to make the row changes inside trigger function visible to the top level sql statement?
Previous:From: Sergey KonoplevDate: 2012-08-09 10:25:06
Subject: Re: Streaming replication failover with 3 servers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group