On Sat, Jan 09, 2021 at 05:26:04PM -0700, "David G. Johnston" <david.g.johnston@xxxxxxxxx> wrote:
> On Saturday, January 9, 2021, raf <raf@xxxxxxx> 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
#!/usr/bin/env python
# pg_extract_views.py - Sample script to extract views from postgres and
# construct equivalent "create or replace view" statements (doesn't handle
# temporary or recursive views).
#
# 20210110 raf <raf@xxxxxxx>
import pgdb # pip install PyGreSQL
# You MUST change these and the password MUST be in ~/.pgpass (mode 600)
DBHOST = 'XXX_localhost'
DBNAME = 'XXX_database_name'
DBUSER = 'XXX_user_name'
# You MUST change this to match your view naming convention
VIEW_NAME_RE = '^XXX_myview_.*$'
def print_views(db):
'''Load all of the views from the database.'''
cursor = db.cursor()
views = {}
for view_name, view_options, view_sourcecode in select_views(cursor):
print('create or replace view %s' % view_name)
print('(')
columns = select_columns(cursor, view_name)
for i in range(len(columns)):
column_name, column_type = columns[i]
print(' %s%s' % (column_name, ',' if i < len(columns) - 1 else ''))
print(')')
if view_options is not None:
options = []
if 'check_option=local' in view_options:
options.append('check_option = "local"')
if 'check_option=cascaded' in view_options:
options.append('check_option = "cascaded"')
if 'security_barrier=true' in view_options:
options.append('security_barrier = true')
if 'security_barrier=false' in view_options:
options.append('security_barrier = false')
print('with')
print('(')
for i in range(len(options)):
print(' %s%s' % (options[i], ',' if i < len(options) - 1 else ''))
print(')')
print('as')
print(view_sourcecode)
print('')
def select_views(cursor):
'''Given a cursor object, return a list of view names.'''
sql = '''
select
c.relname as "view_name",
c.reloptions as "view_options",
v.definition as "view_sourcecode"
from
pg_class c,
pg_views v
where
c.relkind = 'v' and
c.relname ~ %(view_name_re)s and
v.viewname = c.relname
order by
c.relname
'''
cursor.execute(sql, dict(view_name_re=VIEW_NAME_RE))
return cursor.fetchall()
def select_columns(cursor, view_name):
'''Given a cursor and view name, return the column names and types.'''
sql = '''
select
a.attname as "column_name",
format_type(a.atttypid, a.atttypmod) as "column_type"
from
pg_class c,
pg_attribute a
where
c.relkind = 'v' and
c.relname = %(name)s and
a.attrelid = c.oid
order by
a.attnum
'''
cursor.execute(sql, dict(name=view_name))
return cursor.fetchall()
def main():
db = pgdb.connect(host=DBHOST, database=DBNAME, user=DBUSER)
print_views(db)
db.close()
if __name__ == '__main__':
main()
# vi:set ts=4 sw=4:
