How to fix 'missing FROM-clause entry for table'?

问题: I have a function with query. If condition is true, I update a row with new values. I do a query: SELECT * FROM transfer_flight(41313, '2017-08-15 20:00:00+05'); an...

问题:

I have a function with query. If condition is true, I update a row with new values.

I do a query:

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

and I get an error:

ERROR: missing FROM-clause entry for table "flights" LINE 1: SELECT (flights.scheduled_departure < $2) ^ QUERY: SELECT (flights.scheduled_departure < $2) CONTEXT: PL/pgSQL function transfer_flight(integer,timestamp with time >zone) line 7 at IF

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;
$$;

In result I should need to update only one row with input flight_identificator


回答1:

Remove the FROM clause! That does a CROSS JOIN. And you need SET. So:

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;

回答2:

You cannot use flights in the IF statement. Move the condition to the WHERE clause of the query. Also, the variable flight_d assignment is incorrect as the assigned values concern the queried table.

...
AS $$
BEGIN
    UPDATE flights SET
        scheduled_departure = $2,
        scheduled_arrival = $2 + scheduled_arrival - scheduled_departure
    WHERE flights.flight_id = $1
    AND flights.scheduled_departure < $2
    RETURNING 
        flights.flight_id,  
        flights.flight_no,
        flights.departure_airport,
        flights.scheduled_departure,
        flights.arrival_airport,        
        flights.scheduled_arrival;
END;
$$;

As you see, you can do this with a single query so SQL function would be more appropriate.


回答3:

I looks to me like you should get rid of the flight_d variable and of the IF block, and move the logic to the query itself, like :

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 $$

BEGIN

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

END;
$$;
  • 发表于 2019-01-20 11:51
  • 阅读 ( 423 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除