1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
CREATE OR REPLACE FUNCTION pprint_addy(
input NORM_ADDY
) RETURNS VARCHAR
AS $_$
DECLARE
result VARCHAR;
BEGIN
IF NOT input.parsed THEN
RETURN NULL;
END IF;
result := cull_null(input.address::text)
|| CASE WHEN input.preDirAbbrev IS NOT NULL THEN ' ' ELSE '' END
|| cull_null(input.preDirAbbrev)
|| CASE WHEN input.streetName IS NOT NULL THEN ' ' ELSE '' END
|| cull_null(input.streetName)
|| CASE WHEN input.streetTypeAbbrev IS NOT NULL THEN ' ' ELSE '' END
|| cull_null(input.streetTypeAbbrev)
|| CASE WHEN input.postDirAbbrev IS NOT NULL THEN ' ' ELSE '' END
|| cull_null(input.postDirAbbrev)
|| CASE WHEN
input.address IS NOT NULL OR
input.streetName IS NOT NULL
THEN ', ' ELSE '' END
|| cull_null(input.internal)
|| CASE WHEN input.internal IS NOT NULL THEN ', ' ELSE '' END
|| cull_null(input.location)
|| CASE WHEN input.location IS NOT NULL THEN ', ' ELSE '' END
|| cull_null(input.stateAbbrev)
|| CASE WHEN input.stateAbbrev IS NOT NULL THEN ' ' ELSE '' END
|| cull_null(lpad(input.zip::text,5,'0'));
RETURN result;
END;
$_$ LANGUAGE plpgsql;
|
