pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to make the row changes inside trigger function visible to the top level sql statement?
4+ messages / 3 participants
[nested] [flat]
* How to make the row changes inside trigger function visible to the top level sql statement?
@ 2012-08-06 09:14 haifeng liu <haifeng.813@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: haifeng liu @ 2012-08-06 09:14 UTC (permalink / raw)
To: pgsql-admin
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?
Thank all in advance!
From pgsql-admin-owner@postgresql.org Wed Aug 8 03:13:13 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
by mail.postgresql.org (Postfix) with ESMTP id 369218820F3
for <pgsql-admin@postgresql.org>; Tue, 7 Aug 2012 08:06:31 -0300 (ADT)
Received: from mail-lpp01m010-f46.google.com ([209.85.215.46])
by makus.postgresql.org with esmtp (Exim 4.72)
(envelope-from <karthi2itnet@gmail.com>)
id 1SyhcM-0003OL-9h
for pgsql-admin@postgresql.org; Tue, 07 Aug 2012 11:06:30 +0000
Received: by lagy9 with SMTP id y9so1289957lag.19
for <pgsql-admin@postgresql.org>; Tue, 07 Aug 2012 04:06:16 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
d=gmail.com; s 120113;
h=mime-version:date:message-id:subject:from:to:content-type;
bh=5J92jwEYzXyV3aEpfl125DPwOtIqkwojhRdoLgtf4wQ=;
b=gvGLrr+BEfh9MDxnM9I4+hAllvY1T33JdrLY1ODydafBi/519P1CJLNIQVrsRMI7VG
DlXjPOxM02i7qFioISJ5muOC9BWmz9XCHH/Y44+o8ffYnyUlCY3s9YTdb3e3RlJNooSv
ZfMpT8i6H7PV6TcPLF5Zpduyp20EvARcVjKyQsyhYDQc72+XNfvCbJU9atbvo1dYc9ze
SJ71R4l0+wF498S8RLo+Cg3YjVhyoXf/FksTdAr2+2nGtIcGjWGLb+DH01hovHIvo0oB
OaLA6YZkTceqyTR8NFFXQn48g8PbBBHe5QCSMQ9+jPUPl08x5sj4AWnhrViwJ5Y/rxI2
SCRw=MIME-Version: 1.0
Received: by 10.152.132.40 with SMTP id or8mr14120981lab.24.1344337576648;
Tue, 07 Aug 2012 04:06:16 -0700 (PDT)
Received: by 10.112.129.167 with HTTP; Tue, 7 Aug 2012 04:06:16 -0700 (PDT)
Date: Tue, 7 Aug 2012 16:36:16 +0530
Message-ID: <CAM-vHq+wi0jckw+-mHFJeX98FGutkmSPcDnYR75yHinqtHixrg@mail.gmail.com>
Subject: Data migration to sql server 2008
From: karthi keyan <karthi2itnet@gmail.com>
To: pgsql-admin@postgresql.org
Content-Type: multipart/alternative; boundaryô6d042c5ed7b6deb404c6aafb13
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201208/52
X-Sequence-Number: 39873
--f46d042c5ed7b6deb404c6aafb13
Content-Type: text/plain; charset=ISO-8859-1
hello admin,
I am using Postgresql for my application development, which is very robust
and secure to use.
For some interlink application purpose , i need to *migrate data into Sql
server 2008*.
So please refer me, or give some samples, how i can migrate the data
Regards
Karthik
--f46d042c5ed7b6deb404c6aafb13
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
<span style="font-family:verdana,sans-serif">hello admin,</span><br style="font-family:verdana,sans-serif"><span style="font-family:verdana,sans-serif">I am using Postgresql for my application development, which is very robust and secure to use.</span><br style="font-family:verdana,sans-serif">
<br style="font-family:verdana,sans-serif"><span style="font-family:verdana,sans-serif">For some interlink application purpose , i need to <b>migrate data into Sql server 2008</b>.</span><br style="font-family:verdana,sans-serif">
<span style="font-family:verdana,sans-serif">So please refer me, or give some samples, how i can migrate the data </span><br style="font-family:verdana,sans-serif"><br style="font-family:verdana,sans-serif"><span style="font-family:verdana,sans-serif">Regards</span><br style="font-family:verdana,sans-serif">
<span style="font-family:verdana,sans-serif">Karthik</span><br style="font-family:verdana,sans-serif"><br>
--f46d042c5ed7b6deb404c6aafb13--
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to make the row changes inside trigger function visible to the top level sql statement?
@ 2012-08-09 10:53 Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>
parent: haifeng liu <haifeng.813@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Sergey Konoplev @ 2012-08-09 10:53 UTC (permalink / raw)
To: haifeng liu <haifeng.813@gmail.com>; +Cc: pgsql-admin
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 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.ru@gmail.com Skype: gray-hemp Phone: +79160686204
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to make the row changes inside trigger function visible to the top level sql statement?
@ 2012-08-09 11:11 Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>
parent: Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>
0 siblings, 1 reply; 4+ messages in thread
From: Sergey Konoplev @ 2012-08-09 11:11 UTC (permalink / raw)
To: haifeng liu <haifeng.813@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to make the row changes inside trigger function visible to the top level sql statement?
@ 2012-08-09 12:32 Haifeng Liu <liuhaifeng@live.com>
parent: Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>
0 siblings, 0 replies; 4+ messages in thread
From: Haifeng Liu @ 2012-08-09 12:32 UTC (permalink / raw)
To: Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-08-09 12:32 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-08-06 09:14 How to make the row changes inside trigger function visible to the top level sql statement? haifeng liu <haifeng.813@gmail.com>
2012-08-09 10:53 ` Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>
2012-08-09 11:11 ` Sergey Konoplev <sergey.konoplev@postgresql-consulting.com>
2012-08-09 12:32 ` Haifeng Liu <liuhaifeng@live.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox