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