Message-ID: <41A38C87.3080800@ca.afilias.info>
Date: Tue, 23 Nov 2004 14:16:23 -0500
From: Andrew Hammond <ahammond@ca.afilias.info>
Organization: Afilias Canada Corp.
User-Agent: Mozilla Thunderbird 0.8 (X11/20040926)
MIME-Version: 1.0
To: "Gregory S. Williamson" <gsw@globexplorer.com>
Cc: Michael Fuhr <mike@fuhr.org>, Don Drake <dondrake@gmail.com>,
	pgsql-sql@postgresql.org
Subject: Re: NULLS and string concatenation
References: 
 <71E37EF6B7DCC1499CEA0316A256832801D4BB44@loki.wc.globexplorer.net>
In-Reply-To: 
 <71E37EF6B7DCC1499CEA0316A256832801D4BB44@loki.wc.globexplorer.net>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit

-----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-----