f_get_formatted_address

CREATE OR REPLACE FUNCTION f_get_formatted_address(bigint)
  RETURNS character varying AS
$BODY$
DECLARE
    pId                 alias for $1;

    vResult             character varying;
BEGIN

    vResult := '';

    IF EXISTS(SELECT 1 FROM m_partner_address A WHERE A.partner_address_id = pId) THEN
        SELECT CASE WHEN regexp_replace(A.address1, '(\s*|-)', '', 'g') != '' AND (regexp_replace(A.address2, '(\s*|-)', '', 'g') != '' OR regexp_replace(A.address3, '(\s*|-)', '', 'g') != '')
                    THEN A.address1 ||', '
                    WHEN regexp_replace(A.address1, '(\s*|-)', '', 'g') = ''
                    THEN ''
                    ELSE A.address1
                END ||
                CASE WHEN regexp_replace(A.address2, '(\s*|-)', '', 'g') != '' AND regexp_replace(A.address3, '(\s*|-)', '', 'g') != ''
                    THEN A.address2 ||', '
                    WHEN regexp_replace(A.address2, '(\s*|-)', '', 'g') = ''
                    THEN ''
                    ELSE A.address2
                END ||
                CASE WHEN regexp_replace(A.address3, '(\s*|-)', '', 'g') = ''
                    THEN ''
                    ELSE A.address3
                END AS address INTO vResult
        FROM m_partner_address A
        WHERE A.partner_address_id = pId;
    END IF;

    return vResult;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;