Re: Query on exception handling in PL/pgSQL

Am Fr, den 26.11.2004 schrieb diya das um 14:28:
> I am just a beginner in postgreSQL and writing some
> functions in PL/pgSQL. I use the libpq interface in
> the client to call this funtions. My pgsql function
> does an insert to a table and I have opened a
> transaction block before calling my function from the
> client. When the insert operation fails due to unique
> key violation the whole transaction aborts. Is there a
> way I can handle this error in my pgsql funtion rather
> that aborting and not executing the rest of the
> operations?. I have a workaround , But I was wondering
> if there is an inexpensive way of doing it.

Probably you want to do a write operation (UPDATE if the record exists,
INSERT otherwise). You should be aware that trying the INSERT first and
then UPDATEing if it fails is a bad way to do this. If the constraint is
ever accidentially turned of, your data gets corrupted. Provoking
exceptions for situations that are not exceptional is IMHO bad
programming practice anyway.

The good way is either (portable) to do a SELECT to check for existence
of the record or to do the UPDATE first and check FOUND:

UPDATE ...
WHERE key=$1

IF NOT FOUND THEN
INSERT...
END IF;

Sincerely,
Joachim

--
"... ein Geschlecht erfinderischer Zwerge, die fuer alles gemietet werden
koennen." - Bertolt Brecht - Leben des Galilei