Search Postgresql Archives

how to use SAVEPOINT in stored function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux