The postgres-to-docs from klarna-incubator

postgres-to-docs's People

Contributors

Stargazers

Watchers

postgres-to-docs's Issues

Support materialized views

Neither the views and table queries returns materialized views.

One solution is to introduce another select and run
select * from pg_matviews;

Another option (that requires refactoring) is to use this query from a similar python package:
https://github.com/PiskarevSA/pg_autodoc/blob/master/collect_info.py#L68
It returns all tables, views and materialized views in one query

Slightly modified, it could look like this:

SELECT 
	nspname as namespace, 
	relname as tablename,
	pg_catalog.pg_get_userbyid(relowner) AS tableowner,
	pg_class.oid,
	pg_catalog.obj_description(pg_class.oid, 'pg_class') as table_description,
	relacl,
	CASE
		WHEN relkind = 'f' THEN
			'foreign table'
		WHEN relkind = 'm' THEN
			'materialized view'
		WHEN relkind = 's' THEN
			'special'
		WHEN relkind = 'r' THEN
			'table'
		ELSE
			'view'
		END as reltype,
		CASE
			WHEN relkind IN ('m', 'v') THEN
                pg_get_viewdef(pg_class.oid)
			ELSE
                NULL
			END as view_definition
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON (relnamespace = pg_namespace.oid)
WHERE 
	relkind IN ('f', 'm', 's', 'r', 'v')
	AND relname NOT LIKE 'sql_%'
	AND relname NOT LIKE 'pg_%'
	AND nspname <> 'information_schema'