X-Original-To: pgsql-novice@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 5F330474E4F; Fri, 14 Feb 2003 02:54:58 -0500 (EST) Received: from vision1.touristvision.com.au (vision1.corpusglobe.com [202.61.128.2]) by postgresql.org (Postfix) with ESMTP id 8B2B3474E42; Fri, 14 Feb 2003 02:54:56 -0500 (EST) Received: by VISION1 with Internet Mail Service (5.5.2653.19) id ; Fri, 14 Feb 2003 16:10:01 +0800 Message-ID: <3B663B41B350D311AEEF00A0C9254563201A72@VISION1> From: Michael Weaver To: 'Stephan Szabo' , Michael Weaver Cc: "'pgsql-novice@postgresql.org'" , "'pgsql-sql@postgresql.org'" Subject: Re: [SQL] Passing arrays Date: Fri, 14 Feb 2003 16:09:55 +0800 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C2D400.75B77100" X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/170 X-Sequence-Number: 6186 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C2D400.75B77100 Content-Type: text/plain; charset="iso-8859-1" > -----Original Message----- > From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com] > Sent: Friday, 14 February 2003 12:58 PM > To: Michael Weaver > Cc: 'pgsql-novice@postgresql.org'; 'pgsql-sql@postgresql.org' > Subject: Re: [SQL] Passing arrays > > > > On Fri, 14 Feb 2003, Michael Weaver wrote: > > > How do you get an array of elements to be passed to a > stored proc such that > > you can use the array in a SELECT statement in the WHERE clause > > - e.g. WHERE field IN (array) etc... > > A straight WHERE field IN (array) doesn't work. That's attempting to > compare the field directly to the array as a whole (which is > why you get > the particular error you do. I've got around this limitation using strings, but I do find it suprising, that Postgres handles arrays this way. Searching an array in an WHERE IN clause would be a useful task. (namely for passing arrays of values between stored functions) Ok it could be confusing, but I think a number of other DBs employ it to good effect. I'll take a look at the array extension in contrib - it sounds useful. Thanks. ------_=_NextPart_001_01C2D400.75B77100 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: [SQL] Passing arrays

> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
> Sent: Friday, 14 February 2003 12:58 PM
> To: Michael Weaver
> Cc: 'pgsql-novice@postgresql.org'; 'pgsql-sql@postg= resql.org'
> Subject: Re: [SQL] Passing arrays
>
>
>
> On Fri, 14 Feb 2003, Michael Weaver wrote:
>
> > How do you get an array of elements to be pass= ed to a
> stored proc such that
> > you can use the array in a SELECT statement in= the WHERE clause
> >  - e.g.  WHERE field IN (array) etc.= ..
>
> A straight WHERE field IN (array) doesn't work.&nbs= p; That's attempting to
> compare the field directly to the array as a whole = (which is
> why you get
> the particular error you do.

I've got around this limitation using strings, but I do f= ind it suprising, that Postgres handles arrays this way.
Searching an array in an WHERE IN clause would be a usef= ul task. (namely for passing arrays of values between stored functions)

Ok it could be confusing, but I think a number of other D= Bs employ it to good effect.

I'll take a look at the array extension in contrib - it s= ounds useful.

Thanks.

------_=_NextPart_001_01C2D400.75B77100--