| 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: | Whole Thread | Raw Message | 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
| 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 |