Re: Flexibility of views and functions?

On Mon, Jul 13, 2009 at 8:54 PM, Andreas<maps(dot)on(at)gmx(dot)net> wrote:
> Hi,
> I need to do some reporting for projects that have some columns that stay
> the same for every project and then every project brings along some project
> specific stuff.
> Now I've got a big view for everyone of those about 100 projects (and
> growing) that is about 80% the same as every other view.
>
> I'd like to strip the constant part into a central view holding those common
> columns like "tvw_big_thing"
> then have for every project some
> SELECT tvw_big_thing.*, c1, c2, ..., cn ...
> where c1...cn would be project specific.
>
> I'dread the day when mr. boss comes along to tell me he likes tvw_big_thing
> altered.
> All dependend views would have to be dropped and recreated, am I right?
>
> I figured a function as cool, too like
> fct_big_thing(project_id::integer)
> it could do the filtering :)
>
> I'd be cool to have depending views show the "inherited" columns that get
> delivered on call time.

why can't you use a view for what you want to do with a function? you
can nest views...

if you have a situation where a view and a function are both
appropriate...choose a view:

*) views are more flexible...can be queried on any field, not just a
fixed set of inputs
*) views are easier to join with other tables/views
*) the planner will often be able to better discern what is going on
with a view vs a function
*) views have stricter dependency tracking -- the database has higher
probability of blocking a ddl change that would make your function
error (although this can also be a nuisance)

as a consequence of the last point, if you are tables that views
depend on are changing a lot you need to be prepared to have a script
(or a function!) that drops and regenerates your views on command.

merlin