Quick Links

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 14:39:59
Message-ID:	OFF0C71065.258CBBBC-ONC12570CE.004FC312-C12570CE.00508C96@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

Attachment	Content-Type	Size
unknown_filename	text/html	3.7 KB

Browse pgsql-general by date

	From	Date	Subject
Next Message	Timothy Perrigo	2005-12-05 14:42:47	unsubscribe
Previous Message	Rich Doughty	2005-12-05 14:21:47	massive performance hit when using "Limit 1"