Thread: Check a value in array

Hi all.

I have to check if a value is in an array.

I've got a date array in a table and I would like to perform queries
like:

SELECT * FROM table WHERE date IN dates_array;

I've tried using array_contains_date (contrib/array_iterator.sql)
function unsuccessfully:

SELECT array_contains_date(dates_array, date);
WARNING:  plpgsql: ERROR during compile of array_contains_date near line
5. ERROR:  missing .. at end of SQL expression

Anyone can help me?
Thanks,
Marco

On Thu, 2004-04-29 at 13:37, Marco Lazzeri wrote:
> Hi all.
> 
> I have to check if a value is in an array.
> 
> I've got a date array in a table and I would like to perform queries
> like:
> 
> SELECT * FROM table WHERE date IN dates_array;

If you're using 7.4 or later, try:
SELECT * FROM table WHERE date = ANY(dates_array);

This will work without the contrib package.

Il gio, 2004-04-29 alle 19:56, Rod Taylor ha scritto:
> On Thu, 2004-04-29 at 13:37, Marco Lazzeri wrote:
> > Hi all.
> > 
> > I have to check if a value is in an array.
> > 
> > I've got a date array in a table and I would like to perform queries
> > like:
> > 
> > SELECT * FROM table WHERE date IN dates_array;
> 
> If you're using 7.4 or later, try:
> 
>     SELECT * FROM table WHERE date = ANY(dates_array);
> 
> This will work without the contrib package.

Thank you Rod, I forgot to specify I'm using PostgreSQL 7.3.5.

hi,

Marco Lazzeri wrote:

> Il gio, 2004-04-29 alle 19:56, Rod Taylor ha scritto:
> 
>>On Thu, 2004-04-29 at 13:37, Marco Lazzeri wrote:
>>
>>>Hi all.
>>>
>>>I have to check if a value is in an array.
>>>
>>>I've got a date array in a table and I would like to perform queries
>>>like:
>>>
>>>SELECT * FROM table WHERE date IN dates_array;
>>
>>If you're using 7.4 or later, try:
>>
>>    SELECT * FROM table WHERE date = ANY(dates_array);
>>
>>This will work without the contrib package.
> 
> Thank you Rod, I forgot to specify I'm using PostgreSQL 7.3.5.

or, you can write a procedure, and make and it immutable:)

CREATE OR REPLACE FUNCTION in_array (numeric [], numeric) RETURNS 
boolean AS'
declare
array_to alias for $1;
array_value alias for $2;
i integer default 1;
begin
while array_to[i] is not null loop if array_value = array_to[i] then    return true; end if; i := i+1;
end loop;
return false;
end;
'LANGUAGE 'plpgsql' immutable RETURNS NULL ON NULL INPUT SECURITY INVOKER;

change the numeric to your specified type and:

WHERE in_array(dates_array,date) = true

C.

select * from table where date in ('02/02/2004', '02/03/2004' .... )

i'm using pgsql 7.3.2 and it works fine.

Andy.

----- Original Message -----
From: "Marco Lazzeri" <marcomail@noze.it>
To: "Rod Taylor" <pg@rbt.ca>
Cc: <pgsql-sql@postgresql.org>
Sent: Friday, April 30, 2004 11:18 AM
Subject: Re: [SQL] Check a value in array


> Il gio, 2004-04-29 alle 19:56, Rod Taylor ha scritto:
> > On Thu, 2004-04-29 at 13:37, Marco Lazzeri wrote:
> > > Hi all.
> > >
> > > I have to check if a value is in an array.
> > >
> > > I've got a date array in a table and I would like to perform queries
> > > like:
> > >
> > > SELECT * FROM table WHERE date IN dates_array;
> >
> > If you're using 7.4 or later, try:
> >
> > SELECT * FROM table WHERE date = ANY(dates_array);
> >
> > This will work without the contrib package.
>
> Thank you Rod, I forgot to specify I'm using PostgreSQL 7.3.5.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>