Re: [GENERAL] Was: Triggers, Stored Procedures, PHP

Joe Conway <mail@joeconway.com> writes:

> How is a "parameterized view" any different than a set returning SQL function?
> In either case, you've got the same work to do to teach the optimizer how to
> understand it, no? Seems like the todo is just that, teach the optimizer how to
> do better with set-returning SQL functions.

I find almost always that when I wish I had "parameterized views" the view can
be rewritten into more sophisticated views that push the parameterized
constraint outside the view. The problem is that databases usually can't push
the clause back inside. So "parameterized views" usually are a crutch for
working around optimizer limitations but a different limitation than you're
thinking.

For example:

"parameterized view":
 create view view_1 as select count(*) from foo where x = $1

rewritten view and query using it:
 create view view_2 as select x, count(*) from foo group by x;
 select * from view_2 where x = ?

Actually in this case Postgres does fairly well. It does manage to use the
index though it still uses a GroupAggregate instead of a simple Aggregate
node. The run-time is almost as fast as the straightforward query.

-- 
greg