pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCheck a value in array
5+ messages / 4 participants
[nested] [flat]
* Check a value in array
@ 2004-04-29 17:37 Marco Lazzeri <marcomail@noze.it>
0 siblings, 1 reply; 5+ messages in thread
From: Marco Lazzeri @ 2004-04-29 17:37 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Check a value in array
@ 2004-04-29 17:56 Rod Taylor <pg@rbt.ca>
parent: Marco Lazzeri <marcomail@noze.it>
0 siblings, 1 reply; 5+ messages in thread
From: Rod Taylor @ 2004-04-29 17:56 UTC (permalink / raw)
To: Marco Lazzeri <marcomail@noze.it>; +Cc: 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Check a value in array
@ 2004-04-30 08:18 Marco Lazzeri <marcomail@noze.it>
parent: Rod Taylor <pg@rbt.ca>
0 siblings, 2 replies; 5+ messages in thread
From: Marco Lazzeri @ 2004-04-30 08:18 UTC (permalink / raw)
To: Rod Taylor <pg@rbt.ca>; +Cc: 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Check a value in array
@ 2004-04-30 12:14 CoL <col@mportal.hu>
parent: Marco Lazzeri <marcomail@noze.it>
1 sibling, 0 replies; 5+ messages in thread
From: CoL @ 2004-04-30 12:14 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Check a value in array
@ 2004-05-04 15:03 Contact AR-SD.NET <contact@ar-sd.net>
parent: Marco Lazzeri <marcomail@noze.it>
1 sibling, 0 replies; 5+ messages in thread
From: Contact AR-SD.NET @ 2004-05-04 15:03 UTC (permalink / raw)
To: Marco Lazzeri <marcomail@noze.it>; Rod Taylor <pg@rbt.ca>; +Cc: 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@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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-05-04 15:03 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-29 17:37 Check a value in array Marco Lazzeri <marcomail@noze.it>
2004-04-29 17:56 ` Rod Taylor <pg@rbt.ca>
2004-04-30 08:18 ` Marco Lazzeri <marcomail@noze.it>
2004-04-30 12:14 ` CoL <col@mportal.hu>
2004-05-04 15:03 ` Contact AR-SD.NET <contact@ar-sd.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox