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
