Informix to PostgreSQL - Transaction Function

Question

I need to convert my Informix function to PostgreSQL. Problem is that I know PostgreSQL doesn't allow to call BEGIN WORK and COMMIT in function so I don't know how to handle my exceptions and rollback that way.

Function that I want to convert looks like this:

CREATE PROCEDURE buyTicket(pFlightId LIKE transaction.flightId, pAmount LIKE transaction.amount) 
      DEFINE sqle, isame INTEGER; 
      DEFINE errdata CHAR(80); 
      ON EXCEPTION SET sqle, isame, errdata 
         ROLLBACK WORK; 
         IF sqle = -530 AND errdata LIKE '%chkfreespots%' THEN 
            RAISE EXCEPTION -746, 0, 'Not enough free spots';               
         ELSE 
            RAISE EXCEPTION sqle, isame, errdata; 
         END IF 
      END EXCEPTION; 
   BEGIN WORK; 
   INSERT INTO transaction VALUES (0, pFlightId, pAmount); 
   UPDATE tickets SET freeSpots= freeSpots - pAmount
      WHERE flightId = pFlightId; 
   COMMIT WORK; 
END PROCEDURE;

Can you post your initial query that you wan to convert to function.And who told you that you can't handle transactions inside function ? — Houari, May 24 '15 at 09:41
Look to [this answer](http://stackoverflow.com/a/24523639/1216680) as starting point concerning transactions inside function. — Houari, May 24 '15 at 09:42
I want to convert these 2 queries (INSERT and UPDATE), up there is fully functional function in SQL that I want to convert to PostgreSQL. When I want to execute BEGIN WORK in PostgreSQL inside the function i get an error that you can't use it inside the function. — DynamicsNinja, May 24 '15 at 09:48
possible duplicate of [Commit, savepoint, rollback to in PostgreSQL?](http://stackoverflow.com/questions/5448984/commit-savepoint-rollback-to-in-postgresql) — krokodilko, May 24 '15 at 10:14
Just check the manual: http://www.postgresql.org/docs/current/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING — Frank Heikens, May 24 '15 at 10:19
Please tell us which SQL dialect the original DBMS uses. "SQL Function" doesn't define a DBMS. e.g. what exactly does `pFlightId LIKE transaction.flightId` do? But in general, you can't do a `commit` or `rollback` in a function in Postgres. — , May 24 '15 at 10:25
Postgre syntax is "begin transaction" "commit transaction" "rollback transaction" — User2012384, May 24 '15 at 10:53
You don't need BEGIN WORK or COMMIT WORK within a function in PostgreSQL, a function always runs within a transaction. Just use proper plpgsql with exception handling, see manual, and you're fine. — Frank Heikens, May 24 '15 at 11:09

score 1 · Accepted Answer · answered May 24 '15 at 13:21

After reading through your comments I finally managed to fix this one. Here is the PostgreSQL code that is working same as the Informix one I posted above:

CREATE OR REPLACE FUNCTION buyTicket(pFlightId INT, pAmount INT)
RETURNS VOID AS $$
BEGIN
  INSERT INTO transaction(flightId,amount) VALUES (pFlightId, pAmount); 
  UPDATE tickets SET freeSpots = freeSpots - pAmount
  WHERE flightId = pFlightId; 
EXCEPTION
  WHEN others THEN
    IF sqlerrm LIKE '%chkfreespots%' THEN
       RAISE EXCEPTION  'Not enough free spots';
    ELSE
      RAISE;
    END IF;
END;
$$ LANGUAGE plpgsql;

Informix to PostgreSQL - Transaction Function

1 Answers1