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

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

From: haifeng liu <haifeng(dot)813(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: How to make the row changes inside trigger function visible to the top level sql statement?
Date: 2012-08-06 09:14:34
Message-ID: 571AFDFA-85FB-4EF4-B811-F7722DE28F10@gmail.com (view raw or flat)
Thread:
Lists: 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(at)postgresql(dot)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(at)postgresql(dot)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(at)gmail(dot)com>)
	id 1SyhcM-0003OL-9h
	for pgsql-admin(at)postgresql(dot)org; Tue, 07 Aug 2012 11:06:30 +0000
Received: by lagy9 with SMTP id y9so1289957lag.19
        for <pgsql-admin(at)postgresql(dot)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(at)mail(dot)gmail(dot)com>
Subject: Data migration to sql server 2008
From: karthi keyan <karthi2itnet(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Content-Type: multipart/alternative; boundary6d042c5ed7b6deb404c6aafb13
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--

Responses

pgsql-admin by date

Next:From: Haifeng LiuDate: 2012-08-06 10:50:47
Subject: How to make data changes inside trigger function visible to the top level statement ?
Previous:From: Nathan WagnerDate: 2012-08-04 20:32:33
Subject: O

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