62 подписчика
--Тестовый сценарий 6
-- scenario6.sql
-- Комплексный UPDATE для таблицы seats_remain
CREATE OR REPLACE FUNCTION scenario6() RETURNS integer AS $$
DECLARE
test_rec record ;
test_book_ref CHAR(6);
BEGIN
SET application_name = 'scenario6';
-- UPDATE: освобождаем места для завершенных рейсов
WITH completed_flights AS (
SELECT DISTINCT flight_id
FROM bookings.flights
WHERE status = 'Arrived'
AND actual_arrival < (
SELECT actual_arrival
FROM bookings.flights
WHERE actual_arrival IS NOT NULL
ORDER BY random()
LIMIT 1
)
LIMIT 5
)
UPDATE gen.seats_remain sr
SET available = (
SELECT count(*)
FROM bookings.seats s
JOIN bookings.routes r ON r.airplane_code = s.airplane_code
JOIN bookings.flights f ON f.route_no = r.route_no
WHERE f.flight_id = sr.flight_id
)
FROM completed_flights cf
WHERE sr.flight_id = cf.flight_id;
return 0 ;
END
$$ LANGUAGE plpgsql;
Около минуты
19 декабря