Re: Performance difference between ANY and IN, also array syntax

Without anything truly fancy, you could write a proc which dynamically
builds a query string using the IN form out of a array parameter:

You get to do a bunch of string contatenation and you don't get the
luxury of pre-planning, but this technique might work for you. If your
arrays aren't too big, then it might be a winner. I'm sure someone more
knowledgeable may well propose something more elegant.

----
create table test
(
    id int
);

insert into test values(1);
insert into test values(2);
insert into test values(3);


create or replace function dynamic_test(int []) returns setof test as
$$
DECLARE
    query text;
    testrow test;
    ids alias for $1;
    maxidx int := array_upper($1, 1);
    i int;
BEGIN
    query := 'select * from test where id in (';
    -- unroll the array ...
    for i in 1..maxidx loop
        query := query || ids[i];
        if i <> maxidx then
            query := query || ', ';
        end if;
    end loop;
    query := query || ')';

    raise notice 'query: "%"', query;
    -- okay -- eat it now
    for testrow in execute query loop
        return next testrow;
    end loop;

    return;
END;
$$ language plpgsql;

social=# select * from dynamic_test('{2,3}');
NOTICE:  query: "select * from test where id in (2, 3)"
  id
----
   2
   3
(2 rows)

----
----
James Robinson
Socialserve.com