Re: How to keep format of views source code as entered?

On Sat, Jan 09, 2021 at 05:26:04PM -0700, "David G. Johnston" <david.g.johnston@gmail.com> wrote:

> On Saturday, January 9, 2021, raf <raf@raf.org> wrote:
> 
> > Actually, I just had a look at the pg_views system
> > catalog where the source code for views is stored, and
> > it doesn't seem to contain enough information to
> > reconstruct a create view statement. It only contains
> > these columns:
> >
> >   schemaname
> >   viewname
> >   viewowner
> >   definition
> >
> > But definition is just the query itself.
> >
> > There is no list of column names (like there is with
> > procedures in pg_proc).
> >
> > Is all of that information stored somewhere else in the
> > system catalogs?
> >
> 
> Views are relation-like and thus are primarily recorded on pg_class.
> 
> David J.

Hi David,

Thanks. That's very helpful. The following query should
return enough information to re-construct create view
statements:

    select
        c.relname as "view_name",
        a.attname as "column_name",
        format_type(a.atttypid, a.atttypmod) as "column_type",
        v.definition as "view_sourcecode",
        c.reloptions as "view_options"
    from
        pg_class c,
        pg_attribute a,
        pg_views v
    where
        c.relkind = 'v' and
        c.relname like 'myview%' and -- Your naming convention
        a.attrelid = c.oid and
        v.viewname = c.relname
    order by
        c.relname,
        a.attnum;

Note that view_options can look like
"{check_option=local,security_barrier=false}".

Also, this won't find temporary views (I'm probably not
looking in the right place for them).

Also, it probably doesn't handle recursive views.

But for simple views, it should make a basis for
extracting views into files that can be added to a
source code repository.

I've attached a Python script that can output "create
or replace view" statements for the views in a database
that match a particular naming convention.

Note that:

  - Login details are hard-coded and must be changed.
  - View naming convention is hard-coded and must be changed.
  - It doesn't handle temporary or recursive views.
  - It does handle check_option and security_barrier options.
  - All views are output to stdout, not separate files.
  - Comments are not included (code is in post-parse state).
  - It should probably drop then create each view.

I hope it's useful to show how this could be done, but
the loss of comments would bother me. I'd want the
documentation in the source code repository. Having
access to the original source would be much nicer (and
enable auditing views in the database against the views
in the source code repository to see if they've been
changed).

cheers,
raf