how to use SAVEPOINT in stored function

From: Frank(dot)Motzkat(at)ic3s(dot)de
To: pgsql-general(at)postgresql(dot)org
Subject: how to use SAVEPOINT in stored function
Date: 2005-12-05 15:46:39
Message-ID: OF5530AAED.5EF9ECC0-ONC12570CE.00569766-C12570CE.0056A753@notes.ic3s.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2005-12-05 16:15:54 Re: how to use SAVEPOINT in stored function
Previous Message Tom Lane 2005-12-05 14:59:22 Re: ODBC Layer and the now() function