Re: Can't get the field = ANY(array) clause to work...

nboutelier@hotmail.com wrote:
> Has anyone successfully used the "ANY", "ALL", or "SOME" clause using
> arrays? Cant seem to get this to work. Heres the gist of my function
> which returns a SETOF INTEGER[]...
>
> DECLARE
>   id_var INTEGER[];
>   record_var RECORD;
> BEGIN
>   id_var[0] := 1;

I think by default arrays count from 1.

>   id_var[1] := 2;
>   id_var[2] := 3;
>   FOR record_var IN
>     SELECT id FROM myTable WHERE id = ANY(id_var)
>   LOOP
>     RETURN NEXT record_var.id;
>   END LOOP;
>   RETURN;
> END;
>
> I get an empty record set! Any help would be appreciated.

Well, the basic operation works OK here, so I'd check that "id_var"
contains what you think it does...

RAISE NOTICE ''id_var = %'', id_var;


richardh=> SELECT * FROM foo;
  a | b | c
---+---+---
  1 | 0 | 0
  0 | 1 | 0
  2 | 0 | 0
(3 rows)

richardh=> SELECT * FROM bar;
    y
-------
  {1,2}
(1 row)

richardh=> SELECT foo.* FROM foo, bar WHERE a = ANY(y);
  a | b | c
---+---+---
  1 | 0 | 0
  2 | 0 | 0
(2 rows)

--
   Richard Huxton
   Archonet Ltd