[GENERAL] how to use SAVEPOINT in stored function

[Frank . Motzkat]

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




---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq