Re: Conactenating text with null values
От | Csaba Nagy |
---|---|
Тема | Re: Conactenating text with null values |
Дата | |
Msg-id | 1099674994.6361.86.camel@localhost.localdomain обсуждение исходный текст |
Ответ на | Re: Conactenating text with null values (Alvaro Herrera <alvherre@dcc.uchile.cl>) |
Список | pgsql-general |
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) >
В списке pgsql-general по дате отправления: