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

On Mon, Dec 01, 2003 at 09:38:06AM +1100, Alex Satrapa wrote:

> create or replace function get_transactions (INTEGER) returns set of
> record as '
> DECLARE
>    cust_id ALIAS FOR $1;
> BEGIN
> for r in select ... from ... loop
>     return next r;
> end loop;
> return;
> END
> ' language 'plpgsql';
>
> But I would certainly love to have parameterised views :)

Me too.  I've created many functions to extract data that are joined to
other functions.  All in all the result is not as optimal as it could
be, because the optimizer can not poke into the functions, and the
estimates about functions are only guesses.  If one could use
parametrized views instead of functions the whole mess would probably be
more optimal.

Maybe there's a TODO here?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Ninguna manada de bestias tiene una voz tan horrible como la humana" (Orual)

Alvaro Herrera wrote:
> Me too.  I've created many functions to extract data that are joined to
> other functions.  All in all the result is not as optimal as it could
> be, because the optimizer can not poke into the functions, and the
> estimates about functions are only guesses.  If one could use
> parametrized views instead of functions the whole mess would probably be
> more optimal.
>

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.

Joe

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