Re: Conactenating text with null values

Cool, this goes to my "util" mail folder :-)

[snip]
> 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
ISNULL 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)
>