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