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