From: Andrew Hammond <ahammond@ca.afilias.info>
To: Gregory S. Williamson <gsw@globexplorer.com>
Cc: Michael Fuhr <mike@fuhr.org>
Cc: Don Drake <dondrake@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: NULLS and string concatenation
Date: Tue, 23 Nov 2004 14:16:23 -0500
Message-ID: <41A38C87.3080800@ca.afilias.info> (raw)
In-Reply-To: <71E37EF6B7DCC1499CEA0316A256832801D4BB44@loki.wc.globexplorer.net>
References: <71E37EF6B7DCC1499CEA0316A256832801D4BB44@loki.wc.globexplorer.net>
-----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-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ahammond@ca.afilias.info, gsw@globexplorer.com, mike@fuhr.org, dondrake@gmail.com
Subject: Re: NULLS and string concatenation
In-Reply-To: <41A38C87.3080800@ca.afilias.info>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox