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

[James Robinson <jlrobins@xxxxxxxxxxxxxxx>]

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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings