Skip site navigation (1) Skip section navigation (2)

Re: Check a value in array

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 (view raw or flat)
Thread:
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.

In response to

pgsql-sql by date

Next:From: Pallav KalvaDate: 2004-04-30 13:37:25
Subject: Re: Permissions not working
Previous:From: francescosafDate: 2004-04-30 09:25:28
Subject: Re: problem with slow select

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group