Database Reference
In-Depth Information
fmi_lname := CONCAT_WS(' ', CASE fname_mi
WHEN '' THEN NULL ELSE fname_mi END,CASE
trim(lastname) WHEN '' THEN NULL ELSE lastname
END);
RAISE NOTICE 'firstname mi. lastname: %',
fmi_lname;
prefix_fmil := CONCAT_WS('. ', CASE
trim(prefix) WHEN '' THEN NULL ELSE prefix END,
CASE fmi_lname WHEN '' THEN NULL ELSE fmi_lname
END);
RAISE NOTICE 'prefix. firstname mi lastname:
%', prefix_fmil;
pfmil_suffix := CONCAT_WS(', ', CASE
prefix_fmil WHEN '' THEN NULL ELSE prefix_fmil
END, CASE trim(suffix) WHEN '' THEN NULL ELSE
suffix || '.' END);
RAISE NOTICE 'prefix. firstname mi lastname,
suffix.: %', pfmil_suffix;
RETURN pfmil_suffix;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
In this example, we format a person's full name using the magic of NULL propaga-
tion.
NULL propagation is what happens when any or all members of an expression are
null. In the expression:
myvar := null || 'something'
,
myvar
will evaluate
to
null
. PostgreSQL 9.1 introduces a very handy new function named
CONCAT_WS
(concatenate with separator) to take advantage of this effect.
For example:
lastfirst := CONCAT_WS(', ', lastname,
firstname);
