Re: Multiple IN
От | Josh Berkus |
---|---|
Тема | Re: Multiple IN |
Дата | |
Msg-id | web-528861@davinci.ethosmedia.com обсуждение исходный текст |
Ответ на | Multiple IN (Leandro Fanzone <leandro@hasar.com>) |
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления: