SHARE
TWEET

Untitled

a guest Jun 19th, 2017 54 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION find_line(DAY TIMESTAMP, dep_station VARCHAR(100), arr_station VARCHAR(100))
  2.   RETURNS TABLE(line_id INTEGER)--, departue INTEGER,arrival INTEGER)
  3.   AS $$
  4.   DECLARE
  5.   dep_id INTEGER;
  6.   arr_id INTEGER;
  7.   BEGIN
  8.     dep_id = (SELECT id FROM station WHERE dep_station = name);
  9.     arr_id = (SELECT id FROM station WHERE arr_station = name);
  10.     RETURN QUERY SELECT t1.line_id--,  t1.current_station_id as dep, t2.current_station_id as arr
  11.       FROM Trip t1 JOIN Trip t2 ON t1.line_id=t2.line_id
  12.         JOIN Trip_Interval ON trip_interval.line_id = t1.line_id
  13.       WHERE t1.current_station_id = dep_id AND t2.current_station_id = arr_id
  14.             AND (DAY - INTERVAL '3 hours',DAY + INTERVAL '3 hours') OVERLAPS (DAY::DATE + t1.arrival_time,DAY::DATE + t1.arrival_time)
  15.             AND t1.arrival_time < t2.arrival_time
  16.             --AND day::date <= end_date AND day::date >= start_date
  17.       GROUP BY t1.line_id ;
  18.     END
  19.   $$
  20.   LANGUAGE plpgsql;
  21.  
  22. CREATE OR REPLACE FUNCTION basic_price(line INTEGER, "from" INTEGER,"to" INTEGER)
  23.   RETURNS NUMERIC(6,2)
  24.   AS $$
  25.   DECLARE
  26.     OUT INTEGER;
  27.     SECOND INTERVAL;
  28.     FIRST INTERVAL;
  29.   BEGIN
  30.     SECOND = (SELECT arrival_time FROM Trip WHERE line_id = line AND "to" = current_station_id);
  31.     FIRST = (SELECT departure_time FROM Trip WHERE line_id = line AND "from" = current_station_id);
  32.     OUT = (SELECT 3 +  (( 60 * EXTRACT(HOUR FROM SECOND) + EXTRACT(MINUTE FROM SECOND)- 60 * EXTRACT(HOUR FROM FIRST) - EXTRACT(MINUTE FROM FIRST)))/10);
  33.     RETURN OUT;
  34.   END
  35.  
  36.   $$
  37.   LANGUAGE plpgsql;
  38.  
  39. CREATE OR REPLACE FUNCTION timetable(T TIMESTAMP,"from" VARCHAR(100),"to" VARCHAR(100))
  40.   returns TABLE(line INTEGER, train_id text,price NUMERIC(6,2),dep_time INTERVAL,arr_time INTERVAL)
  41. AS $$
  42.   DECLARE
  43.     dep_id INTEGER;
  44.     arr_id INTEGER;
  45.   BEGIN
  46.  
  47.  
  48.     dep_id = (SELECT id FROM station WHERE "from" = name);
  49.     arr_id = (SELECT id FROM station WHERE "to" = name);
  50.     RETURN QUERY (SELECT line_id,E.train_id,basic_price(line_id,dep_id,arr_id),E.departure_time,arrival_time FROM
  51.             (SELECT B.id ,B.train_id,departure_time FROM
  52.           (SELECT line.id,line.train_id FROM find_line(T,"from","to")
  53.           LEFT JOIN line ON (line_id = line.id)) AS B
  54.       LEFT JOIN trip ON
  55.       (B.id = line_id AND current_station_id = dep_id)) AS E LEFT JOIN trip
  56.       ON E.id = line_id AND current_station_id = arr_id) ORDER BY 3,4;
  57.   END
  58. $$
  59.   LANGUAGE plpgsql;
  60.  
  61.  
  62.  
  63. SELECT basic_price(343, 10,11);
  64.  
  65. SELECT * FROM timetable('2017-06-19 18:00:00' :: TIMESTAMP,'Warszawa Centralna' :: VARCHAR(100),
  66.                  'Warszawa Zachodnia' :: VARCHAR(100));
RAW Paste Data
Pastebin PRO Summer Special!
Get 40% OFF on Pastebin PRO accounts!
Top