Re: view management

On Nov 16, 2007 4:43 PM, Ed L. <pgsql(at)bluepolka(dot)net> wrote:
> On Friday 16 November 2007 2:09 pm, Merlin Moncure wrote:
> > you have to rig a build system. if you have a lot of views
> > (which is good), and keeping them up to date is a pain, you
> > have to automate their creation. simplest way to do that is to
> > rig a build system around sql scripts. when you create a view
> > the first time, save it's creation script in a .sql file and
> > replay that when you need it. if you like to get fancy, you
> > can always work solutions around make, etc on top of this.
> > there are other tricks...for example you could grep object
> > dropped by the database and replay them.
>
> That looks about as ugly as can be. Ugh. What it appears to
> boil down to is that views become unusable unless you are
> willing to invest the effort in a complex build system. The DB
> should handle this issue automatically. Does Oracle?

it's not as bad as it looks...but simply keeping your 'create views'
in scripts should be enough. other advantages include ability to
track schema object changes in source code.

> There is a slightly related todo item:
> # Allow VIEW/RULE recompilation when the underlying tables change
>
> Another issue is whether underlying table changes should be
> reflected in the view, e.g. should SELECT * show additional
> columns if they are added after the view is created.

personally, what I would like would be to have the original text of
the view to be stored and replayed by the database in this scenario.
if you do '\d' on a view you may notice that the definition, while
correct, is significantly mangled for some types of queries. this
would also solve the 'select *' issue, which is extremely desirable in
some cases and you could control it in the original create view
statement.

merlin