Date: Sun, 23 Dec 2007 22:33:45 +0100
From: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
To: pgsql-sql@postgresql.org
Subject: Re: returning an array as a list fo single-column rows?
Message-ID: <20071223213345.GA15623@apartia.fr>
Mail-Followup-To: pgsql-sql@postgresql.org
References: <20071223205557.GA14403@apartia.fr>
	<162867790712231327g774b8c5ld488113a3a8252e9@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <162867790712231327g774b8c5ld488113a3a8252e9@mail.gmail.com>
User-Agent: Mutt/1.5.17 (2007-11-01)

On Sun, Dec 23, 2007 at 10:27:09PM +0100, Pavel Stehule wrote:
> On 23/12/2007, Louis-David Mitterrand
> <vindex+lists-pgsql-sql@apartia.org> wrote:
> > Hi,
> >
> > is there a way to return a Pg array as a list of single-column row
> > values?
> >
> > I am trying to circumvent DBI's lack of support for native database
> > arrays and return the list of values from an ENUM as a perl array.
> >
> > Thanks,
> >
> 
> you can solve this problem with conversion to string with const separator
> 
> Like:
> 
> postgres=# select array_to_string(array[1,2,3,4],'|');
>  array_to_string
> -----------------
>  1|2|3|4
> (1 row)
> 
> [pavel@localhost ~]$ perl
> @a = split(/\|/, "1|2|3");
> print $a[1];

Yes I thought about it, but would rather have Pg do the array splitting. 
For instance if the separator occurs in an array element there is no 
built-in escaping:

% select array_to_string(array['ee','dd','rr','f|f'],'|');
 array_to_string 
 -----------------
  ee|dd|rr|f|f

... and then perl would have it all wrong.