sql - 如何修复“缺少表的FROM子句条目”？

我有一个查询功能。如果条件为true，则使用新值更新行。
我要问：

SELECT * FROM transfer_flight(41313, '2017-08-15 20:00:00+05');

我得到一个错误：
错误：缺少表“flights”的FROM子句条目
第1行：选择（航班。航班起飞<$2）
^
查询：选择（航班。航班起飞<$2）
上下文：PL/pgSQL函数transfer_flight（integer，timestamp with time>zone）第7行

CREATE OR REPLACE FUNCTION flightFunc(
    flight_identificator INTEGER,
    new_timestamp timestamp with time zone)
RETURNS TABLE(
    flight_id INTEGER,
    flight_no CHARACTER(6),
    departure_airport character(3),
    scheduled_departure timestamp with time zone,
    arrival_airport character(3),
    scheduled_arrival timestamp with time zone
)
LANGUAGE plpgsql
AS $$
DECLARE
    flight_d timestamp with time zone =  scheduled_arrival -
scheduled_departure;

BEGIN

    IF (flights.scheduled_departure < $2)
    THEN
        UPDATE flights
            scheduled_departure = $2,
            scheduled_arrival = $2 + flight_d
        FROM flights
        WHERE flights.flight_id = $1
        RETURNING
            flights.flight_id,
            flights.flight_no,
            flights.departure_airport,
            flights.scheduled_departure,
            flights.arrival_airport,
            flights.scheduled_arrival;
    END IF;

END;
$$;

因此，我只需要用输入航班识别器更新一行

最佳答案

删除FROM子句！这会产生一个CROSS JOIN。你需要SET。所以：

UPDATE flights
    SET scheduled_departure = $2,
        scheduled_arrival = $2 + flight_d
    WHERE flights.flight_id = $1
RETURNING
    flights.flight_id,
    flights.flight_no,
    flights.departure_airport,
    flights.scheduled_departure,
    flights.arrival_airport,
    flights.scheduled_arrival;