| 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 |