Re: parameterized views?

Linn Kubler sez:
}
} "Joe Conway" <mail(at)joeconway(dot)com> wrote in message
} news:3D74E5E5(dot)9070309(at)joeconway(dot)com(dot)(dot)(dot)
} > 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;
[...]
} It's not execution time that I'm trying to save here, that isn't an issue
} for my database. I'm looking to have a view defined where I can get a
} subset of the records returned based on a parameter. Sure would be a handy
} feature for me. The other option I suppose is to have multiple views
} defiened for each senario and then have the front end pick the appropriate
} view. That just seems like a lot of work and won't be as flexible.

It's not clear to me what you are expecting to gain from a parameterized
view. I'll grant you that it seems like a nice idea (though the line
between a parameterized view and a function that can return sets of rows is
pretty fuzzy), but I think you can get much the same effect without such
hoop-jumping. If you want to simplify the SELECT or FROM clause, you can
use a view. If you want to simplify the WHERE clause you can use a
function. If it's both, use both.

For example, suppose you want the effect of your view above. Try the
following (assuming that f3 is a text type):

CREATE VIEW myview AS ( SELECT f1, f2, f3 FROM mytable );
CREATE FUNCTION myfilter(text, text) RETURNS boolean AS '
select $1 = $2' LANGUAGE SQL;

To use it, you would write:

SELECT * FROM myview WHERE myfilter(f3, 'paramvalue');

The view and the function can be arbitrarily complex, and all you have to
do is pass the right parameters into the function. Does this solve your
problem? Note that I don't think the optimizer is clever enough to delve
into the function's plan and use an index rather than a table scan.

} Thanks again,
} Linn
--Greg