Re: Passing arguments to views

Chris Campbell <chris(at)bignerdranch(dot)com> writes:

> 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

The "right" way to go about this in the original abstract set-theoretic
mindset of SQL is to code the view to retrieve all the rows and then apply
further WHERE clause restrictions to the results of the view.

So for example this:

> CREATE VIEW sales_figures($1, $2) AS
> SELECT ... FROM ... WHERE purchase_date BETWEEN $1 AND $2;

Becomes:

CREATE VIEW sales_figures AS SELECT ... FROM ...

And then you query it with

SELECT * FROM sales_figures WHERE purchase_date BETWEEN $1 AND $2

sales_figures could have any number of joins and complex where clauses
built-in. It could even be an aggregate grouped by some column (like
purchase_date).

This relies on the SQL optimizer to push the WHERE clause down into the view
to the appropriate depth. Postgres isn't always capable of doing so but it
does a pretty decent job.

--
greg