Create Postal Addresses View

PL/pgSQL

CREATE OR REPLACE VIEW addressbaseplus.postal_addresses AS 
         SELECT formatted_address.uprn, formatted_address.rm_udprn, formatted_address.parent_uprn, formatted_address.rpc, formatted_address.multi_occ_count, (initcap(((ltrim(formatted_address.saon || formatted_address.paon) || formatted_address.street_description) || ', '::text) || formatted_address.localitytown) || ' '::text) || formatted_address.postcode::text AS full_address, formatted_address.postcode, formatted_address.geom
           FROM ( SELECT addressbaseplus.geom, addressbaseplus.uprn, addressbaseplus.rm_udprn, addressbaseplus.parent_uprn, addressbaseplus.rpc, addressbaseplus.multi_occ_count, 
           btrim((
                        CASE
                            WHEN addressbaseplus.department_name IS NOT NULL THEN addressbaseplus.department_name::text || ', '::text
                            ELSE ''::text
                        END || 
                        CASE
                            WHEN addressbaseplus.organisation_name IS NOT NULL THEN addressbaseplus.organisation_name::text || ', '::text
                            ELSE ''::text
                        END || 
                        CASE
                            WHEN addressbaseplus.sub_building_name IS NOT NULL THEN addressbaseplus.sub_building_name::text || ', '::text
                            ELSE ''::text
                        END) || 
                        CASE
                            WHEN addressbaseplus.building_name IS NOT NULL THEN addressbaseplus.building_name::text
                            ELSE ''::text
                        END) || ''::text AS saon, ltrim((((
                        CASE
                            WHEN addressbaseplus.pao_start_number IS NOT NULL THEN addressbaseplus.pao_start_number::text
                            ELSE ''::text
                        END || 
                        CASE
                            WHEN addressbaseplus.pao_start_suffix IS NOT NULL THEN addressbaseplus.pao_start_suffix::text
                            ELSE ''::text
                        END) || 
                        CASE
                            WHEN addressbaseplus.pao_end_number IS NOT NULL THEN '-'::text || addressbaseplus.pao_end_number::text
                            ELSE ''::text
                        END) || 
                        CASE
                            WHEN addressbaseplus.pao_end_suffix IS NOT NULL THEN addressbaseplus.pao_end_suffix::text
                            ELSE ''::text
                        END) || 
                        CASE
                            WHEN addressbaseplus.dependent_thoroughfare_name IS NOT NULL THEN (' '::text || addressbaseplus.dependent_thoroughfare_name::text) || ', '::text
                            ELSE ' '::text
                        END) AS paon, 
                        CASE
                            WHEN addressbaseplus.thoroughfare_name IS NOT NULL THEN addressbaseplus.thoroughfare_name::text
                            ELSE ''::text
                        END AS street_description, 
                        CASE
                            WHEN addressbaseplus.dependent_locality IS NOT NULL THEN addressbaseplus.dependent_locality::text || ', '::text
                            ELSE ''::text
                        END || 
                        CASE
                            WHEN addressbaseplus.post_town IS NOT NULL THEN addressbaseplus.post_town::text
                            ELSE ''::text
                        END AS localitytown, addressbaseplus.postcode
                   FROM addressbaseplus.addressbaseplus
                  WHERE addressbaseplus.rm_udprn IS NOT NULL) formatted_address;