Thread: use SAVEPOINT function

Greetings,

I'd like to know is it possible to use "SAVEPOINT" in a function? In the
following example, I got an error when calling this function:

CREATE OR REPLACE function test() returns trigger as $test$
BEGIN
    SAVEPOINT savepoint1;
   ... ...
    COMMIT;
    RETURN NEW;

EXCEPTION
    when RAISE_EXCEPTION THEN
    ROLLBACK TO SAVEPOINT savepoint1;

END;
$test$ language plpgsql;

Thanks a lot,
Emi

The error msg is:

SPI_execute_plan failed executing query "SAVEPOINT savepoint1":
SPI_ERROR_TRANSACTION

>
> I'd like to know is it possible to use "SAVEPOINT" in a function? In
> the following example, I got an error when calling this function:
>
> CREATE OR REPLACE function test() returns trigger as $test$
> BEGIN
>    SAVEPOINT savepoint1;
>   ... ...
>    COMMIT;
>    RETURN NEW;
>   EXCEPTION
>    when RAISE_EXCEPTION THEN
>    ROLLBACK TO SAVEPOINT savepoint1;
> END;
> $test$ language plpgsql;
>
> Thanks a lot,
> Emi
>

On Thu, Apr 14, 2005 at 04:10:18PM -0400, Ying Lu wrote:

> I'd like to know is it possible to use "SAVEPOINT" in a function? In the
> following example, I got an error when calling this function:

No; exceptions in plpgsql are implemented internally using savepoints,
so the function will automatically roll back the whole "begin ... end"
block (remember you can nest blocks if needed.)

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Java is clearly an example of a money oriented programming"  (A. Stepanov)