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  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: ODBC Layer and the now() function
Следующее
От: Tom Lane
Дата:
Сообщение: Re: how to use SAVEPOINT in stored function