To: pgsql-sql@postgresql.org
From: Jasen Betts <jasen@xnet.co.nz>
Subject: Re: Get id of a tuple using exception
Date: 16 Apr 2011 12:13:01 GMT
Organization: Dis (not Dat) Organisation
Lines: 31
Message-ID: <ioc14d$53p$2@reversiblemaps.ath.cx>
References: <BANLkTik0fUKBcdYEnFeLczA0e6Gmt-3D=g@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
User-Agent: slrn/pre1.0.0-18 (Linux)

On 2011-04-14, f vf <fvalentef@gmail.com> wrote:
> --000e0cd2bf6a60c30804a0dec84b
> Content-Type: text/plain; charset=ISO-8859-1
>
> Hello,
> i'm using a pl/sql procedure and I prevent inserting duplicate tuples using
> an exception for example:
>
>   BEGIN
>        INSERT INTO "Triples"(id, subject, predicate, "object")
>                     VALUES (id, sub_i, pred_i, obj_i);
> * EXCEPTION WHEN unique_violation THEN
>                 --do something.
>
> *In some cases I have interest in getting the id of the tuple that was
> already in the table when the exception is triggered. Is there a way for the
> EXCEPTION to return that id instead of using a select to know wich was the
> id of the triple already existing in the table?

if the unique violation is on the ID column that's easy, if it's on
some other constraint then no there's no way to get the id.

do a select first looking for the colliding row

then fall back to an insert.

there may be weaknesses with this, it depends on why you need the Id.


-- 
⚂⚃ 100% natural