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
smime.p7s
Description: S/MIME cryptographic signature
