Re: the case for machine-readable error fields

On Wed, Aug 05, 2009 at 08:57:14PM +0200, Pavel Stehule wrote:
> 2009/8/5 Tom Lane <tgl@sss.pgh.pa.us>:
> > Peter pointed out upthread that the SQL standard already calls out some
> > things that should be available in this way --- has anyone studied that
> > yet?
> 
> yes - it's part of GET DIAGNOSTICS statement
> 
> http://savage.net.au/SQL/sql-2003-2.bnf.html#condition%20information%20item%20name

Just out of interest, how is this supposed to be used?  Also, how many
other SQL statements can be run when a transaction has been aborted?  I
would've thought that only COMMIT or ROLLBACK (and their synonyms) make
sense and GET DIAGNOSTICS seems wrong for this purpose.

I (and most code I've seen) normally structures client calls off to the
database as follows:
 db.execute("""BEGIN;    INSERT INTO foo (a,b) VALUES ($1,$2);   INSERT INTO bar (c,d) VALUES ($3,$4);   SELECT
frub($5,$6);  COMMIT;""", a,b,c,d,e,f);
 

Where would a call to "GET DIAGNOSTICS" sensibly go?  Or is it defined
to return information about the last executed transaction, I can't find
much in the above page or in anything Google gives back about it.

Supporting it is fine from a standards point of view, from a calling
code's correctness point of view it seems much better to send the info
back at a protocol level.

--  Sam  http://samason.me.uk/