Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id A7592633498
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 23 Dec 2009 17:04:46 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 73280-06 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Wed, 23 Dec 2009 21:04:39 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-yx0-f181.google.com (mail-yx0-f181.google.com
	[209.85.210.181])
	by mail.postgresql.org (Postfix) with ESMTP id 6F76F632F3C
	for <pgsql-sql@postgresql.org>; Wed, 23 Dec 2009 17:04:39 -0400 (AST)
Received: by yxe11 with SMTP id 11so6456186yxe.15
	for <pgsql-sql@postgresql.org>; Wed, 23 Dec 2009 13:04:38 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type;
	bh=YhwEPKd6bXb0XendQzMYmuW3nqUmz11iIDZsf4o+xJA=;
	b=jJ8oQeYpiE1xkOmlt3yLeOe4rJ4b6BR4M0wDw3ceK0wyRELA4x4n4ZBIDguC4ZZgZy
	CvJntNREIzhdGF+fLqjY972/r4k/GygltBG5UDgr5hBU0meG9QHG28yiBOmSpdTqfgJk
	HHlijHuY3hZacw4Y7N6xsEQJKETby9jc3sLkk=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=SbOzY/XPURuo0ZlSQJwtVdTTa9ijmd7736z4GnYP30HXpDBm9W0rBCzKi47gM0XaMk
	lGVCI6UcUljbSreu58j1Ks8Xd5I3pmWT1AjI5iIWUVVp6uHcJJ5EDTY2RRFvYr296a59
	5vnthgqFJDX5efCfEK8I3WRkWnMEYo2v51gPk=
MIME-Version: 1.0
Received: by 10.91.106.5 with SMTP id i5mr4533064agm.75.1261602278003;
 Wed, 23
	Dec 2009 13:04:38 -0800 (PST)
In-Reply-To: <4B31D86D.9030000@postnewspapers.com.au>
References: <4B31D619.1090706@obviously.com>
	<4B31D86D.9030000@postnewspapers.com.au>
Date: Wed, 23 Dec 2009 14:04:37 -0700
Message-ID: <37d451f70912231304i3515e575x69b31638962739a6@mail.gmail.com>
Subject: Re: Using || operator to fold multiple columns into one
From: Rosser Schwarz <rosser.schwarz@gmail.com>
To: Craig Ringer <craig@postnewspapers.com.au>
Cc: Bryce Nesbitt <bryce2@obviously.com>, sql pgsql <pgsql-sql@postgresql.org>
Content-Type: text/plain; charset=ISO-8859-1
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200912/82
X-Sequence-Number: 33520

On Wed, Dec 23, 2009 at 1:44 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:

> SELECT COALESCE( t_number::text, t:string::text, t_date::text, t_boolean::text) AS value;

Your invocation of COALESCE is incorrect -- it is n-ary, but it
returns its first non-NULL argument.  Instead, try:

SELECT
   COALESCE(t_number::text, '') ||
   COALESCE(t_string::text, '') ||
   COALESCE(t_date::text, '') ||
   COALESCE(t_boolean::text, '') ||
...

rls

-- 
:wq