how to use SAVEPOINT in stored function
От | Frank.Motzkat@ic3s.de |
---|---|
Тема | how to use SAVEPOINT in stored function |
Дата | |
Msg-id | OF5530AAED.5EF9ECC0-ONC12570CE.00569766-C12570CE.0056A753@notes.ic3s.de обсуждение исходный текст |
Ответы |
Re: how to use SAVEPOINT in stored function
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: