函数错误 - postgresql

Question

我创建了这个函数，它检查飞行的总成本是否已经被正确计算。它应该增加座位的价格，例如400乘以顾客要求的座位数目，例如2.如果客户正确输入总数，例如800，则应该成功返回查询。如果他们没有正确地输入总数，例如1000那么它应该引发异常'价格计算错误'。

CREATE FUNCTION check_totalcost() RETURNS trigger AS $check_totalcost$ 
    DECLARE seatprice int; 
    DECLARE noofseats int; 
    BEGIN 
     SELECT priceperseat INTO seatprice 
     FROM flight 
     WHERE flightid = flightid; 

     SELECT numseats INTO noofseats 
     FROM flightbooking 
     WHERE flightid = flightid; 

     IF(seatprice*noofseats != new.totalcost) THEN 
     RAISE EXCEPTION 'price has been calculated incorrectly'; 

     END IF; 
     RETURN NEW; 
    END; 
    $check_totalcost$ LANGUAGE plpgsql; 
    /* This is used to run the function above */ 
    CREATE TRIGGER insert_totalcost 
    BEFORE INSERT OR UPDATE ON flightbooking 
    FOR EACH ROW EXECUTE PROCEDURE check_totalcost(); 

当我输入了不正确的总额，它引发异常这就是我想要的，但是当我输入了正确的总量，这也引发了时，它应该成功返回查询的除外。

Answer 1

查询

SELECT priceperseat INTO seatprice 
FROM flight 
WHERE flightid = flightid; 

SELECT numseats INTO noofseats 
FROM flightbooking 
WHERE flightid = flightid; 

没有多大意义，因为条件总是为真。 我想你想从插入/更新的记录中获得flightid。使用特殊的记录NEW：

WHERE flightid = NEW.flightid; 

---

您的功能多了一个逻辑错误。座位数量也应该从new记录中获得。

CREATE OR REPLACE FUNCTION check_totalcost() 
RETURNS trigger AS $check_totalcost$ 
    DECLARE seatprice int; 
    BEGIN 
     SELECT priceperseat INTO seatprice 
     FROM flight 
     WHERE flightid = new.flightid; 

     IF seatprice * new.numseats != new.totalcost THEN 
      RAISE EXCEPTION 'price has been calculated incorrectly'; 

     END IF; 
     RETURN NEW; 
    END; 
    $check_totalcost$ LANGUAGE plpgsql;