Re: Multiple IN

Leandro,

There are several ways you can do your 2-column comparison. as Tom
pointed out, there is nothing wrong with the query as you suggested it.
Let me outline a few of the options:

1. Use your interface language (what are you using?) to generate an
ad-hoc query along the line you already suggested, using a loop in the
interface language to generate the query.
SELECT tablea.text FROM tablea WHERE (f1=v1 and f2=v2) OR (f1=v3 and
f2=v4) OR ...
This query will work fine, with the caveat that a few interface
technologies (Microsoft ODBC for one) will restrict the total length of
your query, so test for that. Certainly the above query formation is
likely to be the fastest to execute on large tables (> 10000 rows), as
it involves no iterative loops or subqueries.

2. Load all the values into a "lookups" temporary table from the
interface. Then match against that:
CREATE TEMPORARY TABLE lookups (
v1 INT, v2 INT );
interface language loop:
INSERT INTO lookups VALUES ( $v1, $v2 )
end loop;
SELECT tablea.text
FROM tablea JOIN lookups ON tablea.f1=lookups.v1 and
tablea.f2=lookups.v2
ORDER BY tablea.text;
This has the advantage of requiring less dynamic query generation from
you. It also would probably be a better approach if you are going to
display the results in sets (SELECT ... LIMIT 25 OFFSET 50;). However,
on a straight query, you are likely to get much slower results from this
method; it requires 4 to dozens of database commands instead of one, and
temporary tables cannot be effectively indexed. There are also some
headaches associated with temporary table management.

3. You could create a PL/pgSQL function which would accept two arrays of
values and return true as soon as it found a match:
CREATE FUNCTION match_dual_array ( INT, INT, INT[], INT[]) RETURNS
BOOLEAN
AS ' ... compare each f1 + f2 pair against each v1[] + v2[] array.
Return True if any match, and false if no match.
Then:
SELECT tablea.text FROM tablea
WHERE match_dual_array(f1, f2, v1[], v2[]);
While this function approach has a certain amount of elegance about it
(much simpler queries, for one thing), performance-wise, it's likely to
be the slowest of the three approaches on very large datasets due to the
necessity of procedurally looping through the array for each row in
tablea. If, however, you have relatively few rows in tablea (a few
hundred) this might be the approach for you.

FURTHER READING:
Joe Celko's "SQL for Smarties" see:
http://techdocs.postgresql.org/bookreviews.php

Roberto Mello's and others' contributions to PL/pgSQL documentation and
sample functions:
http://techdocs.postgresql.org/

Have fun!

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco