Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAEC7jm17765; Wed, 14 Nov 2001 07:07:45 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id fAEC3KM48026; Wed, 14 Nov 2001 07:03:21 -0500 (EST) (envelope-from news) From: "Eaglet" X-Newsgroups: comp.databases.postgresql.sql, comp.databases.postgresql.admin, comp.databases.postgresql.hackers Subject: handling exceptions, really not simple... :-(( Date: Wed, 14 Nov 2001 12:51:30 +0100 Organization: ITnet S.p.A. is not responsible of the content of this article as posted by one of its customers. Lines: 52 Message-ID: <9stld9$1cloc$1@news02.it.net> X-Complaints-To: usenet@news02.it.net X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 To: pgsql-hackers@postgresql.org.pgsql-admin@postgresql.org.pgsql-sql@postgresql.org X-Archive-Number: 200111/61 X-Sequence-Number: 2586 Hi 2 everybody! I'm evaluating a database that supports our 3-tier solutions, in addition to Oracle. Postgres is at the moment the candidate n. 1, but I realized it's very hard to translate this type of code (Orqcle PL/SQL): FUNCTION F_EXT(par1, par2 ...) RETURN.... IS BEGIN SAVEPOINT spF_EXT; ...[instructions] INSERT INTO... UPDATE... DELETE... ...[instructions] SAVEPOINT spF_INT; ret := F_INT(par1, par2 ...); IF ret = 'KO' THEN ROLLBACK TO spF_EXT; ELSE IF ret IS NULL THEN ROLLBACK TO spF_INT; END IF; ...[instructions] COMMIT; EXCEPTION WHEN..... ...[instructions] END F_EXT; The goal to achieve is to trap every kind of execution errors without trying to prevent their occurrence. In any case, I usually can't be sure the function (F_EXT) works correcty: an exception could be lanched by an internal function (F_INT), I couldn't know to estabilish what happens and where ... By the way, the evironment is: the browser requests a PHP page that connect to a Data Source (Oracle) and lanch a Stored Procedure; after the execution by Oracle, PHP get the result and returns data or a message to the client. It's very important for me to solve the application logic in the Back-End of the system (Oracle or Postgres): integrity and meaning of function aren't only "commit all" or "rollback all", and when something of wrong happens, I need to know the type of exception and when it occurred. In Oracle I can do this, but in Postgres I've not yet found anything of similar. Can anyone help me? Thanks in advance... Eaglet