Thread: Array to IN or UNION

Array to IN or UNION

From
Jordi Romagos
Date:
I'm doing a procedure and I'm trying to pass a dynamic array into a CURSOR, I found the sentence ANY but it's really slow. Is there any way to convert all the elements in this array to IN condition or one select with unions?
For example,

BEGIN
  example CURSOR (codes integer[] )
   SELECT *
   FROM table_1
    WHERE code IN (3,5,7)

or

  example CURSOR (codes integer[] )
   SELECT *
   FROM table_1
    WHERE codi = 3
   UNION
   SELECT *
   FROM table_1
    WHERE codi = 5
   SELECT *
   FROM table_1
    WHERE codi = 7


DECLARE

  OPEN example(ARRAY[3,5,7]);
END;


Re: Array to IN or UNION

From
Richard Huxton
Date:
Jordi Romagos wrote:
> I'm doing a procedure and I'm trying to pass a dynamic array into a CURSOR,
> I found the sentence ANY but it's really slow. Is there any way to convert
> all the elements in this array to IN condition or one select with unions?
> For example,

I've got a feeling that recent versions of PG actually convert IN to
ANY(<array>) - can you get a sample EXPLAIN ANALYSE output for your
problem query?

--
  Richard Huxton
  Archonet Ltd