Re: parameterized views?

Joe Conway <mail(at)joeconway(dot)com> writes:

> Linn Kubler wrote:
> > Thanks for responding Joe but, not exactly. I'm looking for something
> > more like this:
> > create view myview as
> > select f1, f2, f3 from mytable where f3 = $1;
> > And then be able to call the view passing it a parameter somehow. Possibly
> > like:
> > select * from myview where f3 = 15;
>
> I believe the optimizer will see this as exactly the same query as
> create view myview as
> select f1, f2, f3 from mytable;
> select * from myview where f3 = 15;
> so I don't think you'd get any different execution time.

What you're describing is something I've often wished existed but I've nearly
always realized I didn't really need. I suspect it runs somewhat contrary to
the design of SQL.

To find the way around the problem as Joe Conway demonstrated usually requires
wrapping your head around the idea of having your view represent results for
all possible values of your parameter and then putting a where clause on the
select from the view. You should be able to count on a good database optimizer
to push the where clause into the view and not do more work than necessary.

This keeps the concept of a view as just an imaginary table with consistent
contents regardless of who looks at it. It also ends up being more flexible in
the end than parameters like you describe.

--
greg