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
– Houari, Commented May 24, 2015 at 9:41
Look to this answer as starting point concerning transactions inside function. — Houari
– Houari, Commented May 24, 2015 at 9: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
– DynamicsNinja, Commented May 24, 2015 at 9:48
Just check the manual: postgresql.org/docs/current/interactive/… — Frank Heikens
– Frank Heikens, Commented May 24, 2015 at 10:19
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
– Frank Heikens, Commented May 24, 2015 at 11:09

DynamicsNinja · Accepted Answer · 2015-05-24 13:21:22Z

1

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;

answered May 24, 2015 at 13:21

DynamicsNinja

1771 gold badge3 silver badges17 bronze badges

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

Collectives™ on Stack Overflow

Informix to PostgreSQL - Transaction Function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related