Re: [HACKERS] empty concatenate

I think this is a known bug.
You can try the standard COALESCE function as in:

select coalesce(a,'')||coalesce(b,'') from test;
?column?
--------
AAAABBBB
xxxx
(2 rows)

Jose'

Karel Zak - Zakkr wrote:
> 
>  Hi,
> 
>  I try concatenate text via standard '||' oprerator, but result is
> interesting:
> 
> PgSQL 6.5.3/7.0:
> ~~~~~~~~~~~~~~~
> test=> select * from x;
> a  |b
> ---+---
> AAA|BBB
> xxx|
> (2 rows)
> 
> test=> select a || b from x;
> ?column?
> --------
> AAABBB
>                                   <-------------- empty !
> (2 rows)
> 
> Oracle8:
> ~~~~~~~~
> SVRMGR> select * from x;
> A                                B
> -------------------------------- --------------------------------
> AAA                              BBB
> xxx
> 2 rows selected.
> SVRMGR> select a || b from x;
> A||B
> ----------------------------------------------------------------
> AAABBB
> xxx                                <---------------- not empty !
> 2 rows selected.
> 
>  I fistly think that problem is in the textcat() routine, but PgSQL ignore
> all functions's results if any argument (column) is empty. Example:
> 
> text *
> xxx(text *t1, text *t2)
> {
>         text       *result;
> 
>         result = (text *) palloc(10 + VARHDRSZ);
>         strcpy(VARDATA(result), "happy");
>         VARSIZE(result) = 5 + VARHDRSZ;
>         elog(NOTICE, "RETURN: %s", VARDATA(result));
> 
>         return result;                       /* always return 'happy' */
> }
> 
> 
> test=> select * from x;
> a  |b
> ---+---
> AAA|BBB
> xxx|
> (2 rows)
> 
> test=> select  xxx(a, b) from x;
> NOTICE:  RETURN: happy
> NOTICE:  RETURN: happy
> xxx
> ----
> happy
>                                             <--------- empty ?!
> (2 rows)
> 
> Why is it empty? I believe that is not feature :-)
> 
>                                                 Karel
> 
> PS. sorry, if this is old point, mail-list archive seacher (htdig)
>     not work...
> 
> ----------------------------------------------------------------------
> Karel Zak <zakkr@zf.jcu.cz>              http://home.zf.jcu.cz/~zakkr/
> 
> Docs:        http://docs.linux.cz                    (big docs archive)
> Kim Project: http://home.zf.jcu.cz/~zakkr/kim/        (process manager)
> FTP:         ftp://ftp2.zf.jcu.cz/users/zakkr/        (C/ncurses/PgSQL)
> -----------------------------------------------------------------------
> 
> ************