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

Question

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

score 0 · Answer 1 · answered Jan 15 '19 at 21:34

0

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;

answered Jan 15 '19 at 21:34

Gordon Linoff

1,242,037
58
646
786

I create a function. But when I calling function in query: SELECT * FROM transfer_flight(41313, '2017-08-15 20:00:00+05'); I get error from topic of this question – Jack Jan 15 '19 at 21:36

score 0 · Answer 2 · answered Jan 15 '19 at 22:44

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

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

2 Answers2