I've written some complicated queries that I'd like to save inside
the server and then call from my clients using a short name. For the
queries that require no external information, views are perfect. For
queries that *do* require external information (like a search date
range), I've used functions (written in the SQL language, because I'm
just writing plain SQL queries but using $1, $2 tokens for passed-in
arguments).
When I use these functions, I'm typically joining the results of the
function with other tables. Since much of the work is being done
inside the function, the planner can't use both the inside-function
and outside-function query information when generating a query plan.
Instead, it has to do Function Scans (planning and executing the SQL
inside the function at each execution, I'm assuming) and then
manipulate the output.
Ideally, I'd like to be able to write queries that contain $n tokens
that will be substituted at execution time, save them on the server,
and let the query planner plan the whole query before it's executed.
Basically, writing views with arguments.
For example, a "sales_figures" view that requires start_date and
end_date parameters could be used like this:
CREATE VIEW sales_figures($1, $2) AS SELECT ... FROM ... WHERE purchase_date BETWEEN $1 AND $2;
SELECT * FROM sales_figures('2005-08-22', '2005-09-14') sf JOIN
customers c ON (sf.customer_id = c.customer_id)
What do you think? Is this an interesting feature? Is this the right
way to go about it, or should I try to get the planner to see through
SQL function boundaries (e.g., enhance the function system instead of
enhancing the view system)? Would this be a good project for a newbie
to the code?
I can see that the syntax used above would be problematic: how would
it distinguish that from a call to a sales_figures() function? Any
comments about alternative syntax would be welcome, too!
Thanks!
- Chris