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

nboutelier(at)hotmail(dot)com writes:
> 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[]...

Works for me, modulo the fact that the code is evidently returning
setof int not setof int[]. What PG version are you using?

Welcome to psql 8.1.2, the PostgreSQL interactive terminal.
...
regression=# create table mytable(id int);
CREATE TABLE
regression=# insert into mytable values(1);
INSERT 0 1
regression=# insert into mytable values(2);
INSERT 0 1
regression=# insert into mytable values(4);
INSERT 0 1
regression=# create function foo() returns setof int as $$
regression$# DECLARE
regression$# id_var INTEGER[];
regression$# record_var RECORD;
regression$# BEGIN
regression$# id_var[0] := 1;
regression$# id_var[1] := 2;
regression$# id_var[2] := 3;
regression$# FOR record_var IN
regression$# SELECT id FROM myTable WHERE id = ANY(id_var)
regression$# LOOP
regression$# RETURN NEXT record_var.id;
regression$# END LOOP;
regression$# RETURN;
regression$# END;
regression$# $$ language plpgsql;
CREATE FUNCTION
regression=# select * from foo();
foo
-----
1
2
(2 rows)

regression=#

regards, tom lane