Thread: how to use SAVEPOINT in stored function

Hi community,

I would like using savepoints in my stored functions but I always get the
error

ERROR:  SPI_execute_plan failed executing query "SAVEPOINT my_savepoint":
SPI_ERROR_TRANSACTION
CONTEXT:  PL/pgSQL function "savepoint_test" line 3 at SQL statement

My test function can be found below. I would be very grateful for any hint
which brings progress to my developments ...

-- create table
CREATE TABLE testtable
(
  name varchar(256),
  number int4 DEFAULT 1,
  id varchar(64) NOT NULL,
  CONSTRAINT pk_id PRIMARY KEY (id)
)
WITHOUT OIDS;
ALTER TABLE testtable OWNER TO postgres;

-- insert dummy record
insert into testtable (id,number) values ('id_1', 1);

-- create test function
CREATE OR REPLACE FUNCTION savepoint_test
(
                in_no integer,
                in_name varchar,
                in_id varchar
) RETURNS void
AS $$
BEGIN
        BEGIN
                SAVEPOINT my_savepoint;
                DELETE FROM testtable WHERE number = in_no;
                insert into testtable (id,number) values ('id_2', 2);
--              COMMIT;
                RELEASE SAVEPOINT my_savepoint;
        EXCEPTION
                WHEN unique_violation  THEN
                    ROLLBACK TO my_savepoint;
        END;
END
$$ LANGUAGE plpgsql;

-- call test function
select * from savepoint_test(1, CAST('test-1' AS VARCHAR), CAST('id_1' AS
VARCHAR));


regards,

frank

Frank.Motzkat@ic3s.de writes:
> I would like using savepoints in my stored functions but I always get the
> error
> ERROR:  SPI_execute_plan failed executing query "SAVEPOINT my_savepoint":

In plpgsql, you're supposed to use exception blocks instead.  See
http://www.postgresql.org/docs/8.1/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

            regards, tom lane

On 12/5/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> In plpgsql, you're supposed to use exception blocks instead.

AFAIC, SAVEPOINT usage isn't permitted in any procedural language. But
it also isn't documented. (I couldn't find any phrase mentioning about
this in the docs.)

One more question, if we'd try to use a SAVEPOINT inside an SQL
function, it dumps below error:
  ERROR:  SAVEPOINT is not allowed in a SQL function
  CONTEXT:  SQL function "tmp_func" during startup
But we got below error message if we'd try the same in a pl/pgsql precedure:
  ERROR:  SPI_execute_plan failed executing query "SAVEPOINT svp0":
  SPI_ERROR_TRANSACTION
  CONTEXT:  PL/pgSQL function "tmp_func" line 4 at SQL statement

Is it possible to make the latter error message some more informative
and user-friendly like the one in the previous?


Regards.