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

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;
  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. Thanks! -Nick

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

nboutelier@hotmail.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

I set the array count to start at 1, but still not working. Im using
v8. Ive also read that the ANY clause runs rediculously slow. Is there
another way to convert an array into a record set without writing a
separate function for that? Is it even possible to create a record set
variable in a function? -Nick

The problem was fixed by initializing the array before giving it a
value. Not surprising Postges isnt as popular as it should be. I was by
luck that I found this out - the manual says nothing about init arrays.

DECLARE
  id_var INTEGER[];
  record_var RECORD;
BEGIN
  id_var := '{}';
  id_var[0] := 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;

nboutelier@hotmail.com wrote:
> The problem was fixed by initializing the array before giving it a
> value. Not surprising Postges isnt as popular as it should be. I was by
> luck that I found this out - the manual says nothing about init arrays.

Instead of flippant comments like that, submit docs a patch if you feel
it's necessary.

On Thu, 2 Feb 2006 nboutelier@hotmail.com wrote:

> The problem was fixed by initializing the array before giving it a
> value. Not surprising Postges isnt as popular as it should be. I was by
> luck that I found this out - the manual says nothing about init arrays.

Well, I think that's in part because it got changed because it seemed like
bad behavior (it looks like the array without the initialization should
work in versions past 8.0.2, and it definately does on my reasonably
recent checkout).