CREATE OR REPLACE FUNCTION find_line(DAY TIMESTAMP, dep_station VARCHAR(100), arr_station VARCHAR(100))
  RETURNS TABLE(line_id INTEGER)--, departue INTEGER,arrival INTEGER)
  AS $$
  DECLARE
  dep_id INTEGER;
  arr_id INTEGER;
  BEGIN
    dep_id = (SELECT id FROM station WHERE dep_station = name);
    arr_id = (SELECT id FROM station WHERE arr_station = name);
    RETURN QUERY SELECT t1.line_id--,  t1.current_station_id as dep, t2.current_station_id as arr
      FROM Trip t1 JOIN Trip t2 ON t1.line_id=t2.line_id
        JOIN Trip_Interval ON trip_interval.line_id = t1.line_id
      WHERE t1.current_station_id = dep_id AND t2.current_station_id = arr_id
            AND (DAY - INTERVAL '3 hours',DAY + INTERVAL '3 hours') OVERLAPS (DAY::DATE + t1.arrival_time,DAY::DATE + t1.arrival_time)
            AND t1.arrival_time < t2.arrival_time
            --AND day::date <= end_date AND day::date >= start_date
      GROUP BY t1.line_id ;
    END
  $$
  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION basic_price(line INTEGER, "from" INTEGER,"to" INTEGER)
  RETURNS NUMERIC(6,2)
  AS $$
  DECLARE
    OUT INTEGER;
    SECOND INTERVAL;
    FIRST INTERVAL;
  BEGIN
    SECOND = (SELECT arrival_time FROM Trip WHERE line_id = line AND "to" = current_station_id);
    FIRST = (SELECT departure_time FROM Trip WHERE line_id = line AND "from" = current_station_id);
    OUT = (SELECT 3 +  (( 60 * EXTRACT(HOUR FROM SECOND) + EXTRACT(MINUTE FROM SECOND)- 60 * EXTRACT(HOUR FROM FIRST) - EXTRACT(MINUTE FROM FIRST)))/10);
    RETURN OUT;
  END

  $$
  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION timetable(T TIMESTAMP,"from" VARCHAR(100),"to" VARCHAR(100))
  returns TABLE(line INTEGER, train_id text,price NUMERIC(6,2),dep_time INTERVAL,arr_time INTERVAL)
AS $$
  DECLARE
    dep_id INTEGER;
    arr_id INTEGER;
  BEGIN


    dep_id = (SELECT id FROM station WHERE "from" = name);
    arr_id = (SELECT id FROM station WHERE "to" = name);
    RETURN QUERY (SELECT line_id,E.train_id,basic_price(line_id,dep_id,arr_id),E.departure_time,arrival_time FROM
            (SELECT B.id ,B.train_id,departure_time FROM
          (SELECT line.id,line.train_id FROM find_line(T,"from","to")
          LEFT JOIN line ON (line_id = line.id)) AS B
      LEFT JOIN trip ON
      (B.id = line_id AND current_station_id = dep_id)) AS E LEFT JOIN trip
      ON E.id = line_id AND current_station_id = arr_id) ORDER BY 3,4;
  END
$$
  LANGUAGE plpgsql;



SELECT basic_price(343, 10,11);

SELECT * FROM timetable('2017-06-19 18:00:00' :: TIMESTAMP,'Warszawa Centralna' :: VARCHAR(100),
                 'Warszawa Zachodnia' :: VARCHAR(100));