Re: Catching errors inside a function

On Tue, Sep 04, 2001 at 11:38:09AM -0500, Jeff Eckermann wrote:
> Why not just do a SELECT to check for existence of the proposed insert
> value, and go ahead only if NOT FOUND?
>
> ----- Original Message -----
> From: "Francesco Casadei" <f_casadei@libero.it>
> To: <pgsql-general@postgresql.org>
> Sent: Monday, September 03, 2001 1:07 PM
> Subject: [GENERAL] Catching errors inside a function
>
>
> > I want to execute an INSERT query that may fail due to a primary key
> constraint
> > check. The primary key is on a field whose value is generated randomly by
> > another C function.
> > Is it possible to wrap the insert into a function that checks for failures
> and
> > retry the insert until success? Something like this:
> >
> > function blah
> > {
> >    do {
> > cod = generate code;
> >       execute insert with cod as primary key;
> >    } while (! errors);
> > }
> >
> > I tried with SQL, PL/pgSQL and C functions but the backend aborts the
> > transaction and stops execution of the function as soon as the error is
> thrown.
> > Is there a way to disable this behaviour?
> >
> > Thanks in advance for your help.
> >
> > Francesco Casadei
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
> end of the original message

The table will likely have about 80000-100000 records. A select, an insert and
the mandatory constraint check are a lot of work for just inserting a new row!!
The previous version of the function that generates the code worked this way
(with a SELECT assuring uniqueness of the code), but it was to slow.

    Francesco Casadei