Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 977829FB663
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 27 Mar 2007 12:52:44 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024)
	with ESMTP id 21351-03 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 27 Mar 2007 12:52:30 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75])
	by postgresql.org (Postfix) with ESMTP id 7BD189FA2AF
	for <pgsql-sql@postgresql.org>; Tue, 27 Mar 2007 12:52:37 -0300 (ADT)
Received: from server3.office.archonet.com
	(84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99])
	by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 7D1E814912;
	Tue, 27 Mar 2007 16:45:01 +0100 (BST)
Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36])
	by server3.office.archonet.com (Postfix) with ESMTP id 4F012274143;
	Tue, 27 Mar 2007 16:45:01 +0100 (BST)
Message-ID: <46093BFD.8060007@archonet.com>
Date: Tue, 27 Mar 2007 16:45:01 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: IceDove 1.5.0.9 (X11/20061220)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Sabin Coanda <sabin.coanda@deuromedia.ro>,
	pgsql-sql@postgresql.org
Subject: Re: array_to_string
References: <euaelf$28f8$1@news.hub.org> <20090.1175006990@sss.pgh.pa.us>
	<46093147.6000209@archonet.com> <21430.1175009744@sss.pgh.pa.us>
In-Reply-To: <21430.1175009744@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200703/319
X-Sequence-Number: 27957

Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
>> Tom Lane wrote:
>>> "Sabin Coanda" <sabin.coanda@deuromedia.ro> writes:
>>>> I used the function array_to_string, and I found it ignores NULL values, 
>>>> e.g. array_to_string( 'ARRAY[1,NULL,3]', ',' ) returns '1,3'.
>>> Do you have a better idea?
> 
>> If you're being strict it should presumably return NULL for the whole 
>> string. Or you'd need a "coalesce-to-this" parameter.
> 
> If the whole array is NULL it would return NULL, but that seems
> unhelpful for a single element being NULL ...

It's the least surprising choice though. You'd expect array_to_string() 
to be a loop doing something like:
   output = output || connector || arr[i]
Of course this will produce NULL if any of the inputs are NULL. Now that 
might be unhelpful, but that's NULL for you.

All you're doing is (presumably) adding a hidden test to the loop where 
Sabin seemed to expect COALESCE(arr[i],<something>). I'm not sure either 
is better across the board.

The problem comes with what <something> should be. You're either looking 
at a third parameter or perhaps more cleanly you need a 
coalesce_array(ARRAY,VAL) function which produces an output array where 
all the null elemts are replaced by VAL.

-- 
   Richard Huxton
   Archonet Ltd