sql - Informix 到 PostgreSQL - 事务函数

Question

我需要将 Informix 函数转换为 PostgreSQL。问题是我知道 PostgreSQL 不允许在函数中调用 BEGIN WORK 和 COMMIT，所以我不知道如何处理我的异常并以这种方式回滚。

我要转换的函数如下所示:

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; 

Answer 1

在阅读了您的评论后，我终于设法解决了这个问题。这是与我在上面发布的 Informix 代码一样工作的 PostgreSQL 代码:

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;