Create view, showing all unreserved seats

I have two tables: In one, I save all seats for a specific room. In the other table, data for the reservations of the seats is stored.

Now, i would like to create a view, where I have a column which marks a seat as marked or unmarked.

Or more abstract: I would like to give out true/false in a table column for a view, if a certain number(the seatnumber) appears in the reservationtable.

Thanks in advance

PS: I use postgres

Table definitions开发者_如何转开发:

View "public.seats"

   Column   |  Type   | Modifiers 
------------+---------+-----------
ticketname | text    | 
price      | integer | 
colour     | text    | 
snr        | integer |                      <- thats thes seatnumber, primary key
px         | integer | 
py         | integer | 
rnr        | integer |                      <- the roomnumber, not relevant

Table "public.reservation"

Column |  Type   | Modifiers 
--------+---------+-----------
spnr   | integer | 
snr    | integer |                            <- references seats
knr    | integer | 

---

CREATE OR REPLACE VIEW seat_reserved
AS
SELECT s.snr seat,
       CASE COALESCE(r.snr,-1) WHEN -1 THEN false ELSE true END reserved
 FROM  seats s
       LEFT OUTER JOIN reservation r on r.snr = s.snr

---

select s.*, exists ( select * from reservation r where r.snr = s.snr ) as reserved from seats

---

Select st.*, decode(res.snr,null,'false','true') reserved
from seats st,
reservation res
where res.snr (+) = st.snr