#!/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@raf.org>

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: