Lists: | pgsql-sql |
---|
From: | Marco Lazzeri <marcomail(at)noze(dot)it> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Check a value in array |
Date: | 2004-04-29 17:37:11 |
Message-ID: | 1083260230.20352.7.camel@macbeth.intranet.noze.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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
From: | Rod Taylor <pg(at)rbt(dot)ca> |
---|---|
To: | Marco Lazzeri <marcomail(at)noze(dot)it> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Check a value in array |
Date: | 2004-04-29 17:56:47 |
Message-ID: | 1083261407.30065.303.camel@jester |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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.
From: | Marco Lazzeri <marcomail(at)noze(dot)it> |
---|---|
To: | Rod Taylor <pg(at)rbt(dot)ca> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Check a value in array |
Date: | 2004-04-30 08:18:13 |
Message-ID: | 1083313092.7734.3.camel@macbeth.intranet.noze.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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.
From: | CoL <col(at)mportal(dot)hu> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Check a value in array |
Date: | 2004-04-30 12:14:04 |
Message-ID: | c6tfud$1lai$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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.
From: | "Contact AR-SD(dot)NET" <contact(at)ar-sd(dot)net> |
---|---|
To: | "Marco Lazzeri" <marcomail(at)noze(dot)it>, "Rod Taylor" <pg(at)rbt(dot)ca> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Check a value in array |
Date: | 2004-05-04 15:03:50 |
Message-ID: | 010801c431e9$02e24220$0b00a8c0@forge |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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(at)noze(dot)it>
To: "Rod Taylor" <pg(at)rbt(dot)ca>
Cc: <pgsql-sql(at)postgresql(dot)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
>