Savepoint in PostgreSql Function

Question

I want to use savepoint feature inside a function in PostgreSQL. I read that savepoint cannot be used inside functions in Postgres.

But while I rollback, I want to rollback to a specific point because of which I want to use savepoint. What is the alternative way to do it?

Sample Code

CREATE or replace FUNCTION fn_loadData_Subha()
RETURNS BIGINT 
AS 
$$
DECLARE
    batchId BIGINT;   
    currentTime TIMESTAMP;
    processName VARCHAR(20);
BEGIN


-- Getting current date and time
select TIMESTAMP 'NOW' into currentTime;
select 'ETL_Subha' INTO processName;

SAVEPOINT first_savepoint;
-- Inserting new record into batch log table
INSERT INTO TB_HSS_BATCH_LOG
(PROCESS_NAME,START_DATE,STATUS)
SELECT processName,currentTime,'STARTED';

select currval('TB_HSS_BATCH_LOG_id_seq') INTO batchId;

-- Inserting cost data to history table
    Insert into tb_hss_procedure_cost_hist1
    (HOSP_SYSTEM, HOSP_FACILITY, surgeon_name, procedure_name, department, current_dept_rank, no_of_surgeons, current_imp_cost
     , current_med_surg_cost, current_total_cost, annual_volume, sys_pref_cost,load_seq_no, CREATED_AT)
    Select  
    HOSP_SYSTEM, HOSP_FACILITY,surgeon_name,procedure_name,department,current_dept_rank, no_of_surgeons, current_imp_cost
    , current_med_surg_cost, current_total_cost, annual_volume, sys_pref_cost, batchId,currentTime
    from tb_hss_procedure_cost_stag_in; 

RELEASE SAVEPOINT first_savepoint;    
RETURN 1;


EXCEPTION
WHEN PLPGSQL_ERROR THEN 
  RAISE EXCEPTION '% %', SQLERRM, SQLSTATE; 
  RAISE NOTICE '% %', SQLERRM, SQLSTATE; 
  RETURN 0;
WHEN OTHERS THEN
  RAISE EXCEPTION '% %', SQLERRM, SQLSTATE; 
  RAISE NOTICE '% %', SQLERRM, SQLSTATE; 
  RETURN 0;
  ROLLBACK TRANSACTION;

END;
$$LANGUAGE plpgsql;

You need rollback to first_savepoint: postgresql.org/docs/current/static/sql-rollback-to.html — user330315
– user330315, Commented Mar 7, 2017 at 5:54
You cannot use any transaction related SQL statements inside a PostgreSQL function. — Laurenz Albe
– Laurenz Albe, Commented Mar 7, 2017 at 10:36
rollback to first_savepoint does not work inside function as savepoint cannot be used inside function. I want to know alternative of savepoint.. — Subhasree Mitra
– Subhasree Mitra, Commented Mar 7, 2017 at 11:21

Laurenz Albe · Accepted Answer · 2017-03-07 10:35:56Z

11

The way to use savepoints in PL/pgSQL functions is to use a BEGIN ... EXCEPTION ... END block. Under the hood, this sets a savepoint at BEGIN and rolls back to it when an exception is encountered.

So your code could look like that (I'm not 100% sure if I read your code correctly):

DECLARE
   batchid bigint;
   processname varchar(20) := 'ETL_Subha';
BEGIN
   BEGIN
      INSERT INTO TB_HSS_BATCH_LOG
         (PROCESS_NAME,START_DATE,STATUS)
         VALUES (processname,current_timestamp,'STARTED')
         RETURNING id INTO batchid;
      RETURN 1;
   EXCEPTION
      WHEN OTHERS THEN
         RETURN 0;
   END;
END;

Some general remarks to your code:

batchid is never used.
currenttime is unnecessary – every call to current_time will return the same value in a transaction.
Raising an exception with RAISE will terminate execution. If you want an error to be thrown, just don't catch the original exception; it will be more meaningful than your exception. My code above assumes that you want to catch the exception and want to return 0 instead.
SELECT val INTO variable is the same as variable := value under the hood, but the latter is generally considered more readable.

answered Mar 7, 2017 at 10:35

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Subhasree Mitra Over a year ago

Thanks Laurenz.. The Code is a partial code..so it has some unused stuffs which are actually used in the later part.

Subhasree Mitra Over a year ago

No I dont want to Return 0, I want to rollback to a particular point(alternative to Savepoint as Savepoint cannot be used inside function)..that is the most important reason why I posted this question..

Laurenz Albe Over a year ago

Like I wrote, as soon as you enter the EXCEPTION block, you have rolled back to the savepoint.

Subhasree Mitra Over a year ago

I could not introduce any savepoint in the function as it was not allowed..

Laurenz Albe Over a year ago

I said: The way to use savepoints in PL/pgSQL functions is to use a BEGIN ... EXCEPTION ... END block. Under the hood, this sets a savepoint at BEGIN and rolls back to it when an exception is encountered. So you already have introduced a savepoint if you write such a block.

|

Daniel · Accepted Answer · 2024-05-16 12:01:44Z

0

The code in the other answer did not work for me in PostgreSQL 15, but THIS works (I added a raise exception there):

create or replace function wurst() returns setof companytaxtype as $$ 
declare
x  companytaxtype; begin
-- Begin savepoint
BEGIN 

    -- Change environment for following query
    update companytaxtype set name = 'Ausland' where name = 'Inland';

    -- Query data in changed environment
    for x in
        select * from companytaxtype 
    loop
        return next x;
    end loop;
        
    -- Rollback savepoint
    raise exception 'some name';
EXCEPTION
    WHEN OTHERS THEN RETURN;
END; 
end $$ language plpgsql;

answered May 16, 2024 at 12:01

Daniel

28.2k20 gold badges93 silver badges140 bronze badges

Collectives™ on Stack Overflow

Savepoint in PostgreSql Function

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related