BEGIN EXCEPTION END - small bug?
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
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