Re: Multiple IN

Well, thank you very much for all your help. I was certainly worried for the
OR list, but I see there's no fundamental problem with it. I think I stick
with the point one of your list. I want it to be as portable as possible, so
that discards PL/pgSQL, and as you said, temporary tables for point two are
tricky. I'm working with the libpq++ interface, so there's no problem on
looping the vector to construct dynamically the query.
Thanks again!

Leandro Fanzone

Josh Berkus wrote:

> 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@agliodbs.com
>    and data management solutions       (415) 565-7293
>   for law firms, small businesses        fax 621-2533
>     and non-profit organizations.      San Francisco