Hi list,

i tried a bit with errorhandling and found the following :
(i want to ignore the dublicate key exception)


CREATE OR REPLACE FUNCTION test() RETURNS VARCHAR AS'
BEGIN
  BEGIN
   INSERT INTO table a dublicate key (primary);
  EXCEPTION
   WHEN OTHERS THEN ROLLBACK;
  END;
  RETURN ''test'';
END'LANGUAGE plpgsql;


will result in

ERROR:  SPI_prepare failed for "ROLLBACK": SPI_ERROR_TRANSACTION

I noticed the right syntax would be (works fine)

CREATE OR REPLACE FUNCTION test() RETURNS VARCHAR AS'
BEGIN
  BEGIN
   INSERT INTO table a dublicate key (primary);
  EXCEPTION
   WHEN OTHERS THEN
  END;
  RETURN ''test'';
END'LANGUAGE plpgsql;


Just a hint

Regards Daniel

Wed, 11 Aug 2004 14:20:34 +0200
Daniel Schuchardt <daniel_schuchardt@web.de>
BEGIN EXCEPTION END - small bug?
Daniel Schuchardt <daniel_schuchardt@web.de> writes:
> i tried a bit with errorhandling and found the following :
> (i want to ignore the dublicate key exception)

> ERROR:  SPI_prepare failed for "ROLLBACK": SPI_ERROR_TRANSACTION

You can't use ROLLBACK inside a plpgsql function.  I agree that this
error message leaves something to be desired, though.
[ ... sounds of hacking ... ]
Now it says

regression=# select test();
ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT:  PL/pgSQL function "test" line 5 at SQL statement
regression=#

That might still leave you a bit confused, since you *were* using
an EXCEPTION clause, but offhand it seems about the best we can do.

			regards, tom lane

Fri, 13 Aug 2004 15:16:28 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: BEGIN EXCEPTION END - small bug? 