问题:
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;
$$;