From: Jasen Betts <jasen@xnet.co.nz>
To: pgsql-sql@postgresql.org
Subject: Re: Get id of a tuple using exception
Date: 16 Apr 2011 12:13:01 GMT
Message-ID: <ioc14d$53p$2@reversiblemaps.ath.cx> (raw)
References: <BANLkTik0fUKBcdYEnFeLczA0e6Gmt-3D=g@mail.gmail.com>
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
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: jasen@xnet.co.nz
Subject: Re: Get id of a tuple using exception
In-Reply-To: <ioc14d$53p$2@reversiblemaps.ath.cx>
* 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