sql - 函数语法错误

Question

CREATE FUNCTION check_available_flightID(date)
RETURNS integer AS 
$$
BEGIN 
  SELECT FlightID FROM Flight WHERE FlightDate::date = $1;

  IF NEW.FlightID IS NULL THEN 
  RAISE EXCEPTION 'No filight in the date you enter'
  END IF;

  RETURN query SELECT FlightID FROM Flight WHERE FlightDate::date = $1;
$$
LANGUAGE SQL;   

它说“IF”附近的语法错误......有点困惑为什么有些函数需要 BEGIN 而有些不需要。

谁能给我一个提示？

Answer 1

我不是 Postgres 专家，但我认为 new 用于触发器而不是常规函数。

我想你想要这样的东西:

CREATE FUNCTION check_available_flightID(date)
RETURNS integer AS 
$$
declare newfid int;
BEGIN 
  SELECT FlightID into newfid FROM Flight WHERE FlightDate::date = $1;
  IF newfid IS NULL THEN 
      RAISE EXCEPTION 'No flight in the date you enter';
  END IF;
  RETURN query SELECT FlightID FROM Flight WHERE FlightDate::date = $1;
end;
$$