Найти в Дзене
72 подписчика

--Тестовый запрос - TEMPORARY TABLE

SELECT
  b.book_ref,
  b.book_date,
  t.ticket_no,
  t.passenger_name,
  s.flight_id,
  f.status,
  f.scheduled_departure,
  f.actual_departure,
  bp.seat_no,
  bp.boarding_no
FROM temp_filtered_bookings b
LEFT JOIN tickets t ON b.book_ref = t.book_ref
LEFT JOIN segments s ON t.ticket_no = s.ticket_no
LEFT JOIN flights f ON s.flight_id = f.flight_id
LEFT JOIN boarding_passes bp ON s.ticket_no = bp.ticket_no AND s.flight_id = bp.flight_id
ORDER BY b.book_date, t.ticket_no;
--Сценарий
-- scenario1.sql
-- TEMP TABLES
CREATE OR REPLACE FUNCTION scenario1()
RETURNS integer AS $$
DECLARE
  test_rec record;
  original_app_name text;
  temp_table_name text;
BEGIN
  -- Сохраняем оригинальное application_name
  original_app_name := current_setting('application_name');

  -- Генерируем уникальное имя временной таблицы
  temp_table_name := 'temp_filtered_bookings_' || pg_backend_pid();

  -- Устанавливаем уникальное имя приложения для сессии
  PERFORM set_config('application_name', 'scenario1_' || pg_backend_pid(), false);

  -- Создаем временную таблицу с уникальным именем
  EXECUTE format('
   CREATE TEMP TABLE %I ON COMMIT DROP AS
   WITH random_period AS (
   SELECT
   book_date AS start_date,
   book_date + INTERVAL ''30 days'' AS end_date
   FROM bookings
   ORDER BY RANDOM()
   LIMIT 1
   )
   SELECT b.*
   FROM bookings b
   CROSS JOIN random_period rp
   WHERE b.book_date BETWEEN rp.start_date AND rp.end_date
  ', temp_table_name);

  -- Создаем индексы
  EXECUTE format('CREATE INDEX ON %I (book_ref)', temp_table_name);
  EXECUTE format('CREATE INDEX ON %I (book_date)', temp_table_name);

  EXECUTE format('
   SELECT
   b.book_ref,
   b.book_date,
   t.ticket_no,
   t.passenger_name,
   s.flight_id,
   f.status,
   f.scheduled_departure,
   f.actual_departure,
   bp.seat_no,
   bp.boarding_no
   FROM %I b
   LEFT JOIN tickets t ON b.book_ref = t.book_ref
   LEFT JOIN segments s ON t.ticket_no = s.ticket_no
   LEFT JOIN flights f ON s.flight_id = f.flight_id
   LEFT JOIN boarding_passes bp ON s.ticket_no = bp.ticket_no
   AND s.flight_id = bp.flight_id
   ORDER BY b.book_date, t.ticket_no
  ', temp_table_name)
INTO test_rec;

  -- Восстанавливаем оригинальное application_name
  PERFORM set_config('application_name', original_app_name, false);

  RETURN 0;

EXCEPTION
  WHEN OTHERS THEN
   -- Восстанавливаем application_name в случае ошибки
   PERFORM set_config('application_name', original_app_name, false);
   RAISE;
END;
$$ LANGUAGE plpgsql;
2 минуты