How to write function/trigger that grabs single values from queries for a boolean expression

Question

I am working on a mock airline ticket reservation app using javascript and a postgres database.

--EDITED--

I need to create a function/trigger that will only allow me to insert a new row in my TICKET if the number of tickets for a flight does not exceed the max number of seats for the aircraft.

How do you write a function isolates single values from queries: 1)SELECT count(*) FROM ticket WHERE flight_id = x 2)SELECT maxSeats FROM airplane where aircraft_code = (code for for airplane the correlates with flight).

How do I get single values from these two queries to check (number of seats <= max_seats).

Thanks.

Answer 1

I need to do a transaction that does the following: -INSERT new row in TICKET table that includes a foreign key(flight_id) that references the FLIGHT table. -UPDATE the number of seats(increment by one) in the FLIGHT table for the flight(flight_id) that the ticket was just inserted.

You can derive the "number of seats" by SELECT COUNT(*) FROM TICKET WHERE flight_id = {flight_id}. Adding a number of seats attribute in FLIGHT would be a normalisation error and force you to keep it in sync with the TICKETs for the FLIGHT.

Do I need to use a trigger or function for this that will simply throw an error when I try to commit? or is there a way to set up a special kind of constraint for a column that references the value in another table(ie. FLIGHT.num_seats_booked < AIRPLANE.max_num_seats.)

In a platform that supports it, you could add a CHECK constraint that calls a function to do the necessary checks by querying other tables. As far as I know, while you can do this in Postgres, there are problems re concurrent updates and it is recommended using triggers.

I need to only commit the above transaction if their are still seats available for the flight.

Or should I be using an IF/ELSE query inside of my transaction?

Just looking to get pointed in the general right direction on how to approach this kind of problem

Not relevant with Postgres, but check this thread if you are interested

---

How do i go about writing a function that compares number of tickets for a flight and max_seats for an airplane? I'm not sure how to grab a single value from two queries to compare them and return true or false in a function.

Something like this would work:

SELECT COUNT(*) < (
    SELECT MaxSeats
    FROM Flight
    INNER JOIN Aircraft
    ON Aircraft.AircraftCode = Flight.AircraftCode
    WHERE FlightId = {flightId})    
FROM Ticket
WHERE FlightId = {flightId}