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.
---------------------------(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
