Create Postal Addresses View

PL/pgSQL

CREATE VIEW addressbasepremium.postal_addresses AS SELECT 
blpu.uprn, 
dpa.rm_udprn AS rm_udprn, 
blpu.parent_uprn AS parent_uprn,
blpu.logical_status AS logical_status,
blpu.rpc AS rpc,
blpu.blpu_state AS blpu_state,
blpu.postal_address AS postal_address,
blpu.multi_occ_count AS multi_occ,
class.classification_code as classification,
 
 
 (((((((((
        CASE
            WHEN dpa.organisation_name IS NOT NULL THEN initcap(dpa.organisation_name::text) || ', '::text
            ELSE ''::text
        END || 
        CASE
            WHEN dpa.department_name IS NOT NULL THEN initcap(dpa.department_name::text) || ', '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN dpa.sub_building_name IS NOT NULL THEN initcap(dpa.sub_building_name::text) || ', '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN dpa.building_name IS NOT NULL THEN initcap(dpa.building_name::text) || ', '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN dpa.building_number > 0::numeric THEN dpa.building_number::text || ' '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN dpa.dependent_thoroughfare_name IS NOT NULL THEN initcap(dpa.dependent_thoroughfare_name::text) || ', '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN dpa.throughfare_name IS NOT NULL THEN initcap(dpa.throughfare_name::text) || ', '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN dpa.double_dependent_locality IS NOT NULL THEN initcap(dpa.double_dependent_locality::text) || ', '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN dpa.dependent_locality IS NOT NULL THEN initcap(dpa.dependent_locality::text) || ', '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN dpa.post_town IS NOT NULL THEN initcap(dpa.post_town::text) || ' '::text
            ELSE ''::text
        END) || 
        CASE
            WHEN dpa.postcode IS NOT NULL THEN dpa.postcode
            ELSE ''::character varying
        END::text AS full_address, 
        CASE
            WHEN dpa.postcode IS NOT NULL THEN dpa.postcode
            ELSE ''::character varying
        END AS postcode,
        blpu.geom 
   FROM
   addressbasepremium.abp_delivery_point dpa 
   LEFT OUTER JOIN addressbasepremium.abp_classification class on (dpa.uprn = class.uprn)
   LEFT JOIN addressbasepremium.abp_blpu_record blpu ON dpa.uprn = blpu.uprn;
 
ALTER TABLE addressbasepremium.postal_addresses OWNER TO postgres;