Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id A10179FB62A for ; Sat, 28 Jul 2007 18:00:35 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 37427-06 for ; Sat, 28 Jul 2007 18:00:32 -0300 (ADT) X-Greylist: delayed 00:06:09.401283 by SQLgrey-1.7.5 Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.188]) by postgresql.org (Postfix) with ESMTP id 6C2FB9FB26B for ; Sat, 28 Jul 2007 18:00:32 -0300 (ADT) Received: by rv-out-0910.google.com with SMTP id f1so195182rvb for ; Sat, 28 Jul 2007 14:00:31 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; b=H/O/r0eB1VM1ufIiA5ejeqSvEXnEWypAAxjUmbCxb9fUydTDM6aimTpdn1S6RoOKZsu4Fq91KYgPI9eZs25zFocN4hlhN5vm/UeOwFpCtqaxMVTbU4ZuivbKAr1Fze3Jj7IvV4PouPidbCzoVA0TqcV3NRJhIhdkhHrZaxNgX9A= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=MXo+StN7Xgk//9YXlkHPCUPBKqmCGbAx0sImmlHgkFTKW1cf5hwgk9zczBIvbrfQeIyGPhTKTgMyCrYoqxTQKNnHOdyFV3AvU0mUJSoQqBgZQDWac3KjDLTL8w7ubx3DnKf4nZ7kNS2euv5a+h1z5klRaBrvcHHfPjOXQ7Eel5g= Received: by 10.142.232.20 with SMTP id e20mr228553wfh.1185656061379; Sat, 28 Jul 2007 13:54:21 -0700 (PDT) Received: by 10.142.105.5 with HTTP; Sat, 28 Jul 2007 13:54:21 -0700 (PDT) Message-ID: <95f6bf9b0707281354n2f4772abmaba91132c466b3ac@mail.gmail.com> Date: Sat, 28 Jul 2007 22:54:21 +0200 From: "Marcin Krawczyk" To: pgsql-sql@postgresql.org Subject: raise exception and transaction handling MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_18402_16752463.1185656061348" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/176 X-Sequence-Number: 28911 ------=_Part_18402_16752463.1185656061348 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 RAISE EXCEPTION command in AFTER UPDATE TRIGGER. A piece of code: BEGIN -- some computations bledne := (SELECT g.q_sumka('Poz.' || lps || ' - min. cena: ' || cena || ' ' || waluta ||'; ') FROM g.m_lista WHERE idf = NEW.id); IF EXISTS (SELECT 1 FROM g.m_lista WHERE idf = 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 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. ------=_Part_18402_16752463.1185656061348 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 
RAISE EXCEPTION command in AFTER UPDATE TRIGGER. A piece of code:

BEGIN
-- some computations

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

IF EXISTS (SELECT 1 FROM g.m_lista WHERE idf = 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
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.
------=_Part_18402_16752463.1185656061348--