Thread: Flexibility of views and functions?

Flexibility of views and functions?

From
Andreas
Date:
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.

Re: Flexibility of views and functions?

From
Merlin Moncure
Date:
On Mon, Jul 13, 2009 at 8:54 PM, Andreas<maps.on@gmx.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