Re: Preserving the source code of views

2013/10/22 Stephen Frost <sfrost(at)snowman(dot)net>:

>> 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.

At my current workplace, view definitions (which are very old-school with
revision history etcetera recorded in comments) are stored in PL/PgSQL functions
which are updated and executed whenever views are modified. I'm looking at
adding a wrapper function to automatically detect and recreate dependencies,
not sure how well that would work. We keep track of schema changes via
pg_extractor[1] which integrates quite nicely with GIT.

[1] https://github.com/omniti-labs/pg_extractor

Regards

Ian Barwick