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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
