Monday, December 20, 2010

ANY clause is superior to IN clause. At least on Postgresql :-)












Why is ANY clause superior to IN clause? Postgresql's ANY accepts array, which in turn can be manipulated further.

To illustrate the point, you cannot do this with IN expression:

select * from y 
where lower(email) IN 
    lower( ('ISteve.Jobs@Apple.com','Linus.Torvalds@Linux.com') );

Neither this:

select * from y 
where lower(email) 
    IN ( lower ('ISteve.Jobs@Apple.com','Linus.Torvalds@Linux.com') );



But with ANY clause, you can manipulate the array before passing values to it, a trick you cannot do on IN clause:

create function lower(t text[]) returns text[]
as
$$
select lower($1::text)::text[]
$$ language sql;
 
 
select * from y 
where lower(email) = 
    ANY( lower(ARRAY['ISteve.Jobs@Apple.com'
                     ,'Linus.Torvalds@Linux.com']) );

No comments:

Post a Comment