SHARE
TWEET

Untitled

a guest Oct 16th, 2019 76 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION bookTheSeat(showID INTEGER, Variadic arrSeatID INTEGER[])
  2.     returns TABLE (
  3.         movie_title text,
  4.         cinema_name text,
  5.         cinema_hall_number text,
  6.         seat_row text,
  7.         seat_number text,
  8.         show_start_time TIMESTAMP,
  9.         show_end_time TIMESTAMP,
  10.         ticket_id INTEGER,
  11.         booking_id INTEGER
  12.         )
  13. AS $$
  14.  
  15. DECLARE
  16.     seatID INTEGER;
  17.     bookingID INTEGER;
  18.     COUNT INTEGER;
  19.     BEGIN
  20.  INSERT INTO booking (show_id) VALUES
  21.         (showID);
  22.  bookingID = CURRVAL('booking_id_seq');
  23.  
  24.  FOR i IN 1..array_length(arrSeatID, 1) LOOP
  25.         seatID = arrSeatID[i];
  26.         SELECT COUNT(*) INTO COUNT FROM seat s
  27.             join cinema_hall ch ON ch.id = s.cinema_hall_id
  28.             join show sh ON sh.cinema_hall_id = ch.id
  29.                 AND sh.id = showID
  30.             WHERE s.id = seatID;
  31.  
  32.         IF COUNT <> 1
  33.         THEN RAISE EXCEPTION 'The seat % not from this cinema hall', seatID;
  34.         END IF;
  35.  
  36.         INSERT INTO tickets (booking_id, seat_id, show_id) VALUES
  37.             (bookingID, seatID, showID);
  38.         COUNT := 0;
  39.  END LOOP;
  40.  
  41.  RETURN query
  42.     SELECT m.title, c.name, ch.NUMBER, s.ROW, s.NUMBER, sh.start_time, sh.end_time, t.id, b.id
  43.         FROM booking b
  44.         join tickets t ON t.booking_id = b.id
  45.         join show sh ON sh.id = t.show_id
  46.         join movie m ON m.id = sh.movie_id
  47.         join cinema_hall ch ON ch.id = sh.cinema_hall_id
  48.         join cinema c ON c.id = ch.cinema_id
  49.         join seat s ON s.id = t.seat_id
  50.     WHERE b.id = bookingID;
  51.  
  52. END; $$
  53.  
  54. language 'plpgsql';
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top