-- PostgreSQL

CREATE OR REPLACE FUNCTION f_get_airline_info(my_airline_code CHAR(2))
RETURNS SETOF REFCURSOR
AS $$
DECLARE
    c1 REFCURSOR = 'cursor1';
    c2 REFCURSOR = 'cursor2';
    c3 REFCURSOR = 'cursor3';
BEGIN
  OPEN c1 FOR
    SELECT
      a.name,
     s.*
    FROM
     airlines a
    INNER JOIN
     schedules s ON (a.code = s.airline_code)
    WHERE
     a.code = my_airline_code;
  RETURN NEXT c1;

  OPEN c2 FOR
    SELECT
     a.name,
     f.*
    FROM
     airlines a
    INNER JOIN
     flights f ON (a.code = f.airline_code)
    WHERE
     a.code = my_airline_code;
  RETURN NEXT c2;

  OPEN c3 FOR
    SELECT
     a.name,
     b.*
    FROM
     airlines a
    INNER JOIN
     bookings b ON (a.code = b.airline_code)
    WHERE
     a.code = my_airline_code;
  RETURN NEXT c3;
END;
$$ LANGUAGE plpgsql@

BEGIN;
SELECT f_get_airline_info('AA');
FETCH ALL IN cursor1;
FETCH ALL IN cursor2;
FETCH ALL IN cursor3;
COMMIT;


CREATE OR REPLACE FUNCTION f_get_schedules_by_date_and_country(my_flight_date DATE, my_dept_country CHAR(3))
RETURNS TABLE(
  airline_name VARCHAR(20),
  flight_number INTEGER,
  flight_date DATE,
  dept_country CHAR(2),
  dept_city VARCHAR(20),
  dept_time TIME,
  arrv_country CHAR(2),
  arrv_city VARCHAR(20),
  arrv_time TIME
)
AS $$
BEGIN
  RETURN QUERY(
    SELECT
      a.name,
      f.flight_number,
      f.flight_date,
      s.dept_country,
      s.dept_city,
      s.dept_time,
      s.arrv_country,
      s.arrv_city,
      s.arrv_time
    FROM
      airlines a
    INNER JOIN
      flights f ON (a.code = f.airline_code)
    INNER JOIN
      schedules s ON (f.flight_number = s.flight_number)
    WHERE
      f.flight_date = my_flight_date AND
      s.dept_country = my_dept_country
  );
END;
$$ LANGUAGE plpgsql;

SELECT *
FROM f_get_schedules_by_date_and_country('2012-05-23', 'US');

SELECT *
FROM f_get_schedules_by_date_and_country('2018-01-14', 'US');


CREATE OR REPLACE FUNCTION get_customers_by_flight(my_flight_number INTEGER)
RETURNS TABLE(
  customer_name VARCHAR(100)
)
AS $$
  SELECT
    DISTINCT customer_name
  FROM
    bookings
  WHERE
    flight_number = my_flight_number;
$$ LANGUAGE SQL@

SELECT *
FROM get_customers_by_flight(1000)

SELECT *
FROM get_customers_by_flight(1030)


CREATE OR REPLACE FUNCTION f_get_more_info(my_flight_number INTEGER)
RETURNS SETOF REFCURSOR
AS $$
DECLARE
    c1 REFCURSOR = 'cursor1';
    c2 REFCURSOR = 'cursor2';
BEGIN
  OPEN c1 FOR
    SELECT
      customer_name
    FROM
      get_customers_by_flight(my_flight_number);
  RETURN NEXT c1;

  OPEN c2 FOR
    SELECT
      c.customer_name,
      a.name,
      COUNT(a.name) AS number_of_reservations
    FROM
      airlines a
    INNER JOIN
      bookings b ON (a.code = b.airline_code)
    INNER JOIN
      (SELECT * FROM get_customers_by_flight(my_flight_number)) AS c ON (c.customer_name = b.customer_name)
    WHERE
      EXTRACT(YEAR FROM b.order_date) = '2012'
    GROUP BY
      c.customer_name, a.name
    HAVING
      COUNT(a.name) >= 2
    ORDER BY
      number_of_reservations;
  RETURN NEXT c2;
END;
$$ LANGUAGE plpgsql@

BEGIN;
SELECT f_get_more_info(1030);
FETCH ALL IN cursor1;
FETCH ALL IN cursor2;
COMMIT;