COMP3311 Final Exam 21T3

Question 4 (6 marks)

Write a PLpgSQL function that takes a property ID and returns the full address of that property.

create or replace function address(propID integer) returns text

Addresses are formatted as follows:

For an apartment
- UnitNum/StreetNum StreetName StreetType, Suburb Postode
  e.g. 15/256 Botany Street, Bondi Junction 2022
For a house or townhouse
- StreetNum StreetName StreetType, Suburb Postode
  e.g. 161 Coogee Bay Road, Coogee 2034

If the supplied ID does not exist in the database, return "No such property".

Examples of how the function works:

property=# select address(12345);
     address      
------------------
 No such property
(1 row)

property=# select address(46600);
                  address                  
-------------------------------------------
 9/153 Mill Hill Road, Bondi Junction 2022
(1 row)

property=# select address(46601);
              address               
------------------------------------
 10 Maitland Avenue, Kingsford 2032
(1 row)

property=# select id,ptype,address(id) from Properties order by id limit 5;
  id   |   ptype   |                address                 
-------+-----------+----------------------------------------
 45678 | House     | 10 Moira Crescent, Coogee 2034
 45679 | Apartment | 16/97 Paul Street, Bondi Junction 2022
 45680 | House     | 66 Jellicoe Avenue, Kingsford 2032
 45681 | Apartment | 11/41 Raleigh Street, Coogee 2034
 45682 | House     | 94 Burrows Road, Alexandria 2015
(5 rows)

More examples are in the tests directory.

Instructions:

Define the function address(...) in the file q4.sql
You can make use of any views/functions from the previous questions.
Also, place any additional views used by address() in this file
Additional views must be placed before the q4 definition
Load it into your database using: psql property -f q4.sql
Sample outputs are in the files: tests/*.expected
Check it using: sh check q4 in the q4 directory
Submit it using: give cs3311 exam_q4 q4.sql or Webcms3