Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 07FAB9FB2C6 for ; Sun, 29 Jul 2007 09:06:05 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 98619-03-4 for ; Sun, 29 Jul 2007 09:05:56 -0300 (ADT) X-Greylist: delayed 00:32:19.259206 by SQLgrey-1.7.5 Received: from tur.go2.pl (tur.go2.pl [193.17.41.50]) by postgresql.org (Postfix) with ESMTP id CEB509FB29D for ; Sun, 29 Jul 2007 09:05:46 -0300 (ADT) Received: from poczta.o2.pl (mx2.go2.pl [193.17.41.42]) by tur.go2.pl (o2.pl Mailer 2.0.1) with ESMTP id EA56623048D for ; Sun, 29 Jul 2007 13:33:28 +0200 (CEST) Received: from poczta.o2.pl (mx2 [127.0.0.1]) by poczta.o2.pl (Postfix) with ESMTP id 9DB5A74808A for ; Sun, 29 Jul 2007 13:33:25 +0200 (CEST) Received: from michalk (gql66.internetdsl.tpnet.pl [83.3.167.66]) by poczta.o2.pl (Postfix) with ESMTP for ; Sun, 29 Jul 2007 13:33:25 +0200 (CEST) Message-ID: <002d01c7d1d4$475e41e0$3e01a8c0@michalk> From: "Michal Kedziora" To: References: <95f6bf9b0707281354n2f4772abmaba91132c466b3ac@mail.gmail.com> Subject: Re: raise exception and transaction handling Date: Sun, 29 Jul 2007 13:33:25 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_002A_01C7D1E5.0A9DACD0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.3028 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.776 tagged_above=0 required=5 tests=BAYES_40=-0.185, HTML_MESSAGE=0.001, RCVD_IN_BL_SPAMCOP_NET=1.96 X-Spam-Level: * X-Archive-Number: 200707/178 X-Sequence-Number: 28913 This is a multi-part message in MIME format. ------=_NextPart_000_002A_01C7D1E5.0A9DACD0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, Marcin=20 I'm not familiar with PREPARE TRANSACTION maby it could be done in that = way.=20 But, you can use a EXCEPTION clause, and there put your insert.=20 IF EXISTS (SELECT 1 FROM g.m_lista WHERE idf =3D NEW.id) THEN RAISE EXCEPTION 'CENY NIE SPELNIAJA WARUNKOW! %', = rtrim(bledne); END IF; EXCEPTION=20 when RAISE_EXCEPTION then=20 INSERT INTO g.m_proba VALUES (1,2); END; It will work corect if you have only one RAISE EXCEPTION,becouse = RAISE_EXCEPTION concern every exception called by RAISE command. I hope that will help.=20 ----- Origi nal Message -----=20 From: Marcin Krawczyk=20 To: pgsql-sql@postgresql.org=20 Sent: Saturday, July 28, 2007 10:54 PM Subject: [SQL] raise exception and transaction handling Hi, I have a problem with transaction handling. What I need to do is = execute an INSERT command that would not be canceled by the=20 RAISE EXCEPTION command in AFTER UPDATE TRIGGER. A piece of code:=20 BEGIN -- some computations=20 bledne :=3D (SELECT g.q_sumka('Poz.' || lps || ' - min. cena: ' || = cena || ' ' || waluta ||'; ') FROM g.m_lista WHERE idf =3D NEW.id); IF EXISTS (SELECT 1 FROM g.m_lista WHERE idf =3D NEW.id) THEN RAISE EXCEPTION 'CENY NIE SPELNIAJA WARUNKOW! %', rtrim(bledne); BEGIN INSERT INTO g.m_proba VALUES (1,2); -- this is the operation I need to = perform but the RAISE EXCEPTION above cancels it out=20 PREPARE TRANSACTION 'a'; COMMIT PREPARED 'a'; END; END IF; I tried to do it as shown above, with PREPARE and COMMIT but it's not = working.=20 ------=_NextPart_000_002A_01C7D1E5.0A9DACD0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi, Marcin
 
I'm not familiar with PREPARE TRANSACTION maby it could be done in that way.=20
But, you can use a EXCEPTION clause, = and=20 there put your insert.
 
IF EXISTS (SELECT 1 FROM g.m_lista WHERE idf =3D NEW.id)=20 THEN
           =20         RAISE EXCEPTION 'CENY NIE = SPELNIAJA=20 WARUNKOW! %', rtrim(bledne);
END IF;
 
EXCEPTION 
 when = RAISE_EXCEPTION=20 then 
     INSERT INTO g.m_proba VALUES (1,2);
END;
 
It will work corect if you have only = one RAISE EXCEPTION,becouse = RAISE_EXCEPTION=20 concern every exception called by RAISE command.
I hope that will help.
----- Origi
nal Message -----
From:=20 Marcin=20 Krawczyk
Sent: Saturday, July 28, 2007 = 10:54=20 PM
Subject: [SQL] raise exception = and=20 transaction handling

Hi,
I have a problem with transaction=20 = handling. What I need to do is execute=  an INSERT command that would not be&n= bsp;canceled by the 
RAISE EXCEPTION command = in AFTER UPDATE TRIGGER. A piece of code:=20

BEGIN
-- some computations

bledne :=3D (SELECT=20 g.q_sumka('Poz.' || lps || ' - min. cena: ' || cena || ' ' || waluta = ||'; ')=20 FROM g.m_lista WHERE idf =3D NEW.id);

IF EXISTS (SELECT 1 FROM = g.m_lista=20 WHERE idf =3D NEW.id) THEN
RAISE EXCEPTION 'CENY NIE SPELNIAJA = WARUNKOW! %',=20 rtrim(bledne);
BEGIN
INSERT INTO g.m_proba VALUES (1,2); -- this = is the=20 operation I need to perform but the RAISE EXCEPTION above cancels it = out=20
PREPARE TRANSACTION 'a';
COMMIT PREPARED 'a';
END;
END=20 IF;

I tried to do it as shown=20 above, with PREPARE and COMMIT but it's not = working.=20
------=_NextPart_000_002A_01C7D1E5.0A9DACD0--