Re: Conactenating text with null values

On Fri, Nov 05, 2004 at 12:51:11PM +0100, Csaba Nagy wrote:
> [snip]
> > SELECT TRIM(TRIM(TRIM(COALESCE(s_house,'') || ' ' ||
> > COALESCE(s_post_dir,'')) || ' ' || COALESCE(s_street ,'')) || ' ' ||
> > COALESCE(s_suffix,'')) FROM parcels WHERE s_pin = '1201703303520';
> >
> > The TRIMs are to remove surplus spaces from inside the result string.
> Avoiding the inner trims:
>
> SELECT TRIM(
> COALESCE(s_house || ' ','')
> || COALESCE(s_post_dir || ' ','')
> || COALESCE(s_street || ' ','')
> || COALESCE(s_suffix,'')
> ) FROM parcels WHERE s_pin = '1201703303520';
>
> Looks a bit more understandable :-)

But it's still too cumbersome. How about creating a new operator? With
the example below the query would simply be

SELECT s_host ||~ s_post_dir ||~ s_street ||~ s_suffix
FROM parcels
WHERE s_pin = '1201703303520';

alvherre=# CREATE 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;
CREATE FUNCTION
alvherre=# SELECT text_concat_nulls_with_an_embedded_space('foo', NULL);
text_concat_nulls_with_an_embedded_space
------------------------------------------
foo
(1 fila)

alvherre=# SELECT text_concat_nulls_with_an_embedded_space(NULL, 'foo');
text_concat_nulls_with_an_embedded_space
------------------------------------------
foo
(1 fila)

alvherre=# SELECT text_concat_nulls_with_an_embedded_space('bar', 'foo');
text_concat_nulls_with_an_embedded_space
------------------------------------------
bar foo
(1 fila)
alvherre=# CREATE OPERATOR ||~ (PROCEDURE = text_concat_nulls_with_an_embedded_space, LEFTARG = text, RIGHTARG = text);
CREATE OPERATOR
alvherre=# SELECT 'hi' ||~ 'foo' ||~ null ||~ null ||~ 'bar' ||~ 'baz' ||~ null ||~ 'bye';
?column?
--------------------
hi foo bar baz bye
(1 fila)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"The first of April is the day we remember what we are
the other 364 days of the year" (Mark Twain)