From: Craig Ringer <craig@postnewspapers.com.au>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: maboyz <thabani.moyo@distributel.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: @@Error equivalent in Postgresql
Date: Fri, 23 Oct 2009 18:59:34 +0800
Message-ID: <4AE18C96.8000007@postnewspapers.com.au> (raw)
In-Reply-To: <162867790910211932n5772f2a5ge43c3dcd45662316@mail.gmail.com>
References: <25995788.post@talk.nabble.com>
<162867790910211027k3daa588bwbd31d4ce8de23eb8@mail.gmail.com>
<25998338.post@talk.nabble.com>
<162867790910211932n5772f2a5ge43c3dcd45662316@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: craig@postnewspapers.com.au, pavel.stehule@gmail.com, thabani.moyo@distributel.ca
Subject: Re: @@Error equivalent in Postgresql
In-Reply-To: <4AE18C96.8000007@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox