Re: the case for machine-readable error fields

2009/8/6 Sam Mason <sam(at)samason(dot)me(dot)uk>:
> On Wed, Aug 05, 2009 at 08:57:14PM +0200, Pavel Stehule wrote:
>> 2009/8/5 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.

typically in SQL/PSM (stored procedures - look on GET DIAGNOSTICS
statement in plpgsql doc), maybe in ecpg. Other's environments raise
exception - so you can get some data from exception or from special
structures related to environment - php, ruby, .NET etc

Pavel
>
> --
>  Sam  http://samason.me.uk/
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>