Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id CB7EB9FB550 for ; Mon, 18 Jun 2007 04:17:15 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 53033-01 for ; Mon, 18 Jun 2007 04:17:15 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id F2F929FB52A for ; Mon, 18 Jun 2007 04:17:14 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id B6E6C31434; Mon, 18 Jun 2007 09:17:13 +0200 (MET DST) From: "Sabin Coanda" X-Newsgroups: pgsql.sql Subject: Re: array_to_string Date: Mon, 18 Jun 2007 10:17:19 +0300 Organization: Hub.Org Networking Services Lines: 46 Message-ID: References: <20090.1175006990@sss.pgh.pa.us> X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-RFC2646: Format=Flowed; Original To: pgsql-sql@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/123 X-Sequence-Number: 28613 "Tom Lane" wrote in message news:20090.1175006990@sss.pgh.pa.us... > "Sabin Coanda" 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? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > I found the function would be useful to build dynamic statements that includes array items. For instance I have a function with an array argument of integer. In that function I'd like to send that argument by a dynamic string to other function. If array_to_string would return the whole elements, including NULLs, I'd be able to build that statement. But with the present behavior, the result statement damages the array structure. On the other hand, I saw there is available its complement function string_to_array. It would be nice to be able to reverse a string built by array_to_string, with string_to_array, getting the original array. This could happen if the function would consider the NULLs as well. A simple 'NULL' string would be enough to fulfil this reversing process, so that array_to_string( ARRAY[1,NULL,3], ',' ) would returns '1,NULL,3'. A problem will occur when it would be applied to string arrays, because NULL string, and 'NULL' string value would have the same result. This could be solved if the string arrays would be formed with the same rules as for SQL constant syntax. I mean with quotes. So that, array_to_string( ARRAY[ 'a', 'NULL', NULL, 'b'], '/' ) would return not a/NULL/NULL/b , but 'a'/'NULL'/NULL/'b'. Consequently, string_to_array function should interpret this result. Regards, Sabin