Re: NULLS and string concatenation

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Gregory S. Williamson wrote:
| Someone on this list provided me with a rather elegant solution to
this a few weeks ago:
|
| CREATE OR REPLACE FUNCTION
text_concat_nulls_with_an_embedded_space(text, text)
| RETURNS text
| AS 'SELECT CASE WHEN $1 IS NULL THEN $2 WHEN $2 IS NULL THEN $1 ELSE
$1 || '' '' || $2 END'
| LANGUAGE sql;

Ugly. As the previous poster mentioned, handling NULLs is what COALESCE
is for.

CREATE OR REPLACE
FUNCTION text_concat_nulls_with_an_embedded_space(text,text)
IMMUTABLE CALLED ON NULL INPUT RETURNS text
AS 'SELECT COALESCE($1 || '' '' || $2, $2, $1);'
LANGUAGE sql;

| CREATE OPERATOR ||~ (PROCEDURE =
text_concat_nulls_with_an_embedded_space, LEFTARG = text, RIGHTARG = text);
|
| And I call it as:
| SELECT (trim(s_directio) ||~ trim(s_house) ||~ trim(s_post_dir) ||~
trim(s_street) ||~ trim(s_suffix)) as street ... (yadda yadda)
|
| Deals quite neatly with the NULLs in some of the columns.

Or my personal favourite:

CREATE OR REPLACE FUNCTION comma_concat (text,text)
IMMUTABLE CALLED ON NULL INPUT RETURNS text
AS 'SELECT COALESCE ($1 || '','' || $2, $2);'
LANGUAGE sql;

CREATE AGGREGATE comma_concat (
~    BASETYPE=text,
~    SFUNC=comma_concat,
~    STYPE=text
);

Which is handy for 1:n reports like

SELECT grade, comma_concat($name) AS members
FROM test_results
GROUP BY grade;

- --
Andrew Hammond    416-673-4138    ahammond@ca.afilias.info
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFBo4yHgfzn5SevSpoRAgjrAJ9M5WwQE1FOaxcs7o45KjdKZF6AQACgkCKS
V+qljFHFtYbOMcRU+7SawmY=
=xqTu
-----END PGP SIGNATURE-----