-----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 [EMAIL PROTECTED] 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-----
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])