All of the above is correct. Let me summarize to make sure everything's clear.
Precedence
- foo-[oracle/postgresql].xql
- foo.xql
- foo.tcl
As noted, there are no errors if a query is in specific as well as generic .xql files. The outer join example from a previous point illustrates why. PG isn't the only RDBMS to support standard syntax. We started out with the notion that we might support several RDBMSs (and we may, yet, who knows). All that use the standard SQL 92 outer join syntax can share the generic form, only Oracle needs the oracle-only form. In other words the SQL 92 version isn't in the PG .xql file because it's not PG-specific but standard. All standard queries should go in the generic query file.
Does this make sense?
It would be good to strip out the queries from the .tcl files, yes. In fact if any one wants to play with the Query Extractor to investigate automatically doing this it might be an interesting exercise (I've considered this many times but just never seem to have the time to dig in).
My longer-term notion is to change the syntax of the db_* API:
db_foo query_name
grabs query from query file (as db_map does today) while
db_foo -query $sql
would execute the inline query. The idea being that some queries will always be db-specific (check out the schema browser if you don't believe me!) and might as well be inline (the schema browser has entirely different proc lib files for the two dbs) and we still want the ability to conditionally build up one or another query using db_map, perhaps passing the result to a proc that does the actual call.
This would make clear whether or not a QD entry exists, would allow the QD to give an error if a query's not found (it now just passes the query from the Tcl file), etc.
