Re: Optimisation of INTERSECT expressions

Phil,

> So I suppose I'll have to find a more sophisticated way to generate my
> queries. Imagine a user interface for a search facility with various
> buttons and text entry fields. At the moment, for each part of the search
> that the user has enabled I create a string of SQL. I then compose them
> into a single statement using INTERSECT. Each sub-query always returns the
> same attribute, but to make things complicated they may come from different
> tables. It now seems that I'll have to merge the queries more thoroughly.
> Does anyone have any suggestions about how to do this? I'd like a nice
> general technique that works for all possible subqueries, as my current
> composition with INTERSECT does.

I've done this but it involves a choice between a lot of infrastrucure for
fully configurable queries, or limiting user choice. The former option
requires that you construct reference tables holding what search fields are
available, what kind of values they hold, and what operators to use while
querying, as well as a table storing the joins used for the various tables
that can be queried.

Based on that, you can construct dynamically a query on any field or combo of
fields listed in your reference tables.

If search options are more constrained, you can simply take the easier path of
hard-coding the query building blocks into a set-returning function. I do
this all the time for Web search interfaces, where the user only has about 9
things to search on.

--
-Josh Berkus
Aglio Database Solutions
San Francisco