Re: Preserving the source code of views

Brian,

* Brian Crowell (brian(at)fluggo(dot)com) wrote:
> On Sun, Oct 20, 2013 at 4:24 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > No, and it's very unlikely that there ever will be, because it's
> > completely against the system structure at a number of levels. However,
> > there's more than one way to skin this cat. Many people keep their DDL as
> > text in some external CMS, and just load it into the database again after
> > any change. If you write the view as "CREATE OR REPLACE VIEW ..." in your
> > DDL, this is easy, at least for cases where you're not changing the set of
> > columns provided by the view.
>
> All right, so I'm getting the picture. Postgres developers are keeping
> their code in larger scripts and committing them to databases as
> batches, rather than editing them in place. We had a pretty slick
> setup going with developers writing code against a live dev database,
> with system documentation pulled from the SQL object comments, and the
> ability to script the whole database to/from git when needed for
> source control purposes.

Sounds pretty neat.

> In some raw form, the workflow here is more like: write a module in a
> script file that re-commits all its objects to the database, and then
> execute that whole batch when testing or deploying. Commit that script
> to git. If I want to keep my documentation scheme, I'll need to pull
> the comments out of that file. A harder sell to my developers, but not
> impossible.
>
> I noticed some objects (like views) are very picky about dependents.
> Do you drop all the module's objects at the beginning of the script,
> just in case there's a change in the number or types of columns? That
> seems tricky, especially considering there will be modules that depend
> on yours.
>
> You also mentioned an external CMS. Any suggestions?

I'm a big fan of git, but if you really want to keep things in-database
and track dependencies, etc, it occurs to me that you might be able to
use an actual table in the database to store the raw form of your view
definitions and then have an SP or something which can link up that
table to the actual views in the database and then you can look at
dependencies through the PG system catalogs...

Haven't really thought this through completely, but wanted to mention
the idea as it might help you.

Thanks,

Stephen