Re: Passing arguments to views

On Feb 2, 2006, at 23:33, Greg Stark wrote:

> 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

That was a very simplistic example and didn't illustrate my point --
I apologize. I was trying to think of something succinct and
illustrative for a quick mailing list post but came up short.

Maybe a better example would be a situation where you want to do
substitutions in places other than the WHERE clause? There's no way
to "push" that out to the calling query. But even in this simple
case, the easier-to-grok syntax of making a view look like a function
(and codifying the options for restricting the results as arguments
to the view) is a nice win in terms of readability and maintainability.

I was hoping that people would overlook my bad example because
they've had the need for a "view with arguments" tool in their own
work, and the conversation would just be about how it could be
implemented. :)

I'll try to distill a better example from some of the projects I'm
working on.

Thanks!

- Chris