Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] empty concatenate

From: Jose Soares <jose(at)sferacarta(dot)com>
To: Karel Zak - Zakkr <zakkr(at)zf(dot)jcu(dot)cz>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] empty concatenate
Date: 1999-12-23 16:38:34
Message-ID: 3862500A.2F26802E@sferacarta.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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(at)zf(dot)jcu(dot)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)
> -----------------------------------------------------------------------
> 
> ************

In response to

pgsql-hackers by date

Next:From: sszaboDate: 1999-12-23 16:58:13
Subject: Re: [HACKERS] empty concatenate
Previous:From: Robert BadaraccoDate: 1999-12-23 16:08:07
Subject: libpg and remote access

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group