Message-ID: <4AE18C96.8000007@postnewspapers.com.au>
Date: Fri, 23 Oct 2009 18:59:34 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Pavel Stehule <pavel.stehule@gmail.com>
CC: maboyz <thabani.moyo@distributel.ca>, pgsql-sql@postgresql.org
Subject: Re: @@Error equivalent in Postgresql
References: <25995788.post@talk.nabble.com>	
	<162867790910211027k3daa588bwbd31d4ce8de23eb8@mail.gmail.com>	
	<25998338.post@talk.nabble.com>
	<162867790910211932n5772f2a5ge43c3dcd45662316@mail.gmail.com>
In-Reply-To: <162867790910211932n5772f2a5ge43c3dcd45662316@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Pavel Stehule wrote:

> So the
> programming based on returning state codes is very obsolete, and
> little bit difficult. You can emulate, but any protected block creates
> inner transaction and this should negative effect on speed - and it
> are some lines more.

I'd like to second and emphasise this. Attempting to use return codes
will cause you unnecessary grief. It might be worth it to maintain
compatibility in other code between Pg and MS-SQL, but I'm not convinced
- there are enough other differences that you'll probably need to
maintain fairly different versions of the functions anyway. In many
cases (mainly where no flow control is required) your T-SQL functions
can become simple SQL functions anyway.

I suspect in  the long run you'll be better off handling errors by
letting exceptions bubble up so that the caller can handle it or let it
bubble up in turn. The exception will include an informative error code.
You can then get that error code via exception information if you trap
the exception in PL/PgSQL or via your application driver interface
(JDBC/ODBC/etc) as the SQLSTATE if it bubbles up to a top-level statement.

I've found PostgreSQL's exception-based error handling a real
life-saver, especially when working with programming languages that also
use exceptions. When I'm working in Java, for example, a sanity check
deep in some PL/PgSQL function may raise an exception that propagates
through to the top-level SQL statement, causing the JDBC driver to throw
a Java SQLException that in turn propagates up to code that's in a
position to do something about the problem.

--
Craig Ringer