From konkodi221@rediffmail.com Thu Oct 6 07:01:18 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5EA9DD9CD7 for ; Thu, 6 Oct 2005 04:01:18 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 93527-01 for ; Thu, 6 Oct 2005 07:01:07 +0000 (GMT) Received: from rediffmail.com (unknown [203.199.83.39]) by svr1.postgresql.org (Postfix) with SMTP id 92536D8833 for ; Thu, 6 Oct 2005 04:01:06 -0300 (ADT) Received: (qmail 15351 invoked by uid 510); 6 Oct 2005 06:55:46 -0000 Date: 6 Oct 2005 06:55:46 -0000 Message-ID: <20051006065546.15349.qmail@webmail29.rediffmail.com> Received: from unknown (61.11.63.192) by rediffmail.com via HTTP; 06 oct 2005 06:55:46 -0000 MIME-Version: 1.0 From: "padmanabha konkodi" Reply-To: "padmanabha konkodi" To: pgsql-sql@postgresql.org Subject: catching errors in function Content-type: multipart/alternative; boundary="Next_1128581746---0-203.199.83.39-15296" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.956 required=5 tests=[DNS_FROM_RFC_ABUSE=0.374, FROM_ENDS_IN_NUMS=0.516, HTML_50_60=0.095, HTML_IMAGE_ONLY_08=1.97, HTML_MESSAGE=0.001, MSGID_FROM_MTA_HEADER=0] X-Spam-Level: ** X-Archive-Number: 200510/47 X-Sequence-Number: 22900 This is a multipart mime message --Next_1128581746---0-203.199.83.39-15296 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline =A0=0Ahello sql developers,=0A=0Ai have written function in which while ex= ecuting it may throw error.=0Aif the error thrown i want rollback the trans= action if not i want commit.=0A=0Ahow can i achive this task.=0A=0Ahow can = catch exception thrown in the function=0A=0Aplz help me=0A=0A=0Aregards=0Ak= onkodi=0A --Next_1128581746---0-203.199.83.39-15296 Content-type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

=0A 
=0Ahello sql developers,
=0A
=0Ai have written funct= ion in which while executing it may throw error.
=0Aif the error thrown = i want rollback the transaction if not i want commit.
=0A
=0Ahow can = i achive this task.
=0A
=0Ahow can catch exception thrown in the func= tion
=0A
=0Aplz help me
=0A
=0A
=0Aregards
=0Akonkodi
= =0A=0A

=0A

=0A=0A --Next_1128581746---0-203.199.83.39-15296-- From sad@bankir.ru Thu Oct 6 07:15:03 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2FF1FD6E83 for ; Thu, 6 Oct 2005 04:15:03 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 07311-06 for ; Thu, 6 Oct 2005 07:14:59 +0000 (GMT) Received: from moon.antora.ru (moon.antora.ru [83.222.21.34]) by svr1.postgresql.org (Postfix) with ESMTP id 6CE0CDA302 for ; Thu, 6 Oct 2005 04:14:58 -0300 (ADT) Received: from [217.26.17.26] (helo=[10.0.0.72]) by moon.antora.ru with esmtpsa (TLSv1:DHE-RSA-AES256-SHA:256) (Exim 4.51 (FreeBSD)) id 1ENPxz-000PX6-BA; Thu, 06 Oct 2005 11:14:59 +0400 Message-ID: <4344CE37.2090407@bankir.ru> Date: Thu, 06 Oct 2005 11:11:51 +0400 From: "Eugene E." User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050517) X-Accept-Language: en-us, en MIME-Version: 1.0 To: padmanabha konkodi Cc: pgsql-sql@postgresql.org Subject: Re: catching errors in function References: <20051006065546.15349.qmail@webmail29.rediffmail.com> In-Reply-To: <20051006065546.15349.qmail@webmail29.rediffmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.061 required=5 tests=[AWL=0.061] X-Spam-Level: X-Archive-Number: 200510/48 X-Sequence-Number: 22901 > i have written function in which while executing it may throw error. > if the error thrown i want rollback the transaction if not i want commit. BEGIN; SELECT you_function(args); ... many sql commands; ... COMMIT; that's all if your function raises an error whole transaction will be aborted when the COMMIT is called. From desoi@pgedit.com Thu Oct 6 13:51:31 2005 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 76C8DDA3A6 for ; Thu, 6 Oct 2005 10:51:31 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 00283-06 for ; Thu, 6 Oct 2005 13:51:26 +0000 (GMT) Received: from a2s4.a2hosting.com (a2s4.a2hosting.com [64.9.205.220]) by svr1.postgresql.org (Postfix) with ESMTP id 643B4DA3A4 for ; Thu, 6 Oct 2005 10:51:27 -0300 (ADT) Received: from user-1120q9s.dsl.mindspring.com ([66.32.105.60] helo=[192.168.1.10]) by a2s4.a2hosting.com with esmtpsa (TLSv1:RC4-SHA:128) (Exim 4.52) id 1ENW9j-0000p5-LO; Thu, 06 Oct 2005 09:51:31 -0400 In-Reply-To: <20051006065546.15349.qmail@webmail29.rediffmail.com> References: <20051006065546.15349.qmail@webmail29.rediffmail.com> Mime-Version: 1.0 (Apple Message framework v733) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <36747E9C-455C-4C74-A49F-46AEFB12118D@pgedit.com> Cc: pgsql-sql@postgresql.org Content-Transfer-Encoding: 7bit From: John DeSoi Subject: Re: catching errors in function Date: Thu, 6 Oct 2005 09:51:24 -0400 To: "padmanabha konkodi" X-Mailer: Apple Mail (2.733) X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - a2s4.a2hosting.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - pgedit.com X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.012 required=5 tests=[AWL=0.012] X-Spam-Level: X-Archive-Number: 200510/55 X-Sequence-Number: 22908 On Oct 6, 2005, at 2:55 AM, padmanabha konkodi wrote: > i have written function in which while executing it may throw error. > if the error thrown i want rollback the transaction if not i want > commit. > > how can i achive this task. > > how can catch exception thrown in the function The function itself cannot start or rollback a transaction (functions are always executed in the context of a transaction). But I think you can use a SAVEPOINT to accomplish your request: http://www.postgresql.org/docs/8.0/interactive/sql-savepoint.html And see this page on how to catch exceptions: http://www.postgresql.org/docs/8.0/interactive/plpgsql-control- structures.html#PLPGSQL-ERROR-TRAPPING John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL