Postgres: Support Materialized Views

[mjpieters]

In Postgres, a Materialized View is basically a cached copy of a query, and has a lot in common with views and tables. They are treated just like tables when querying, and just like views in that they have an underlying query that produced the data.

However, they are not showing up in the tbls documentation, because they are neither a view, nor a table, and the current information schema query doesn't include materialized views anyway (they are listed in the pg_catalog.pg_class table but not in information_schema.tables).

Current materialized views can be listed with the pg_catalog.pg_matviews view. The following version of the tableRows query adds in materialized views:

SELECT DISTINCT cls.oid AS oid, cls.relname AS table_name, tbl.table_type AS table_type, tbl.table_schema AS table_schema
FROM pg_catalog.pg_class cls
INNER JOIN pg_namespace ns ON cls.relnamespace = ns.oid
INNER JOIN (SELECT table_name, table_type, table_schema
FROM information_schema.tables
WHERE table_schema != 'pg_catalog' AND table_schema != 'information_schema'
AND table_catalog = $1
UNION SELECT matviewname as table_name, 'MATERIALIZED VIEW' as table_type, schemaname as table_schema
FROM pg_catalog.pg_matviews
WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema'
) tbl ON cls.relname = tbl.table_name AND ns.nspname = tbl.table_schema
ORDER BY oid;

If we then update the view definition handling to use the pg_catalog.pg_get_viewdef() function (which works for both views and materialized views) the code can also handle MATERIALIZED VIEWs. Note: pg_get_viewdef() takes an OID, but currently the OID is stored as a string, this should really be a uint64!

This is a rough sketch of how this could work:

		// (materialized) view definition
		if tableType == "VIEW" || tableType == "MATERIALIZED VIEW" {
			viewDefRows, err := p.db.Query(`SELECT pg_catalog.pg_get_viewdef($1);`, tableOid)
			defer viewDefRows.Close()
			if err != nil {
				return errors.WithStack(err)
			}
			for viewDefRows.Next() {
				var tableDef sql.NullString
				err := viewDefRows.Scan(&tableDef)
				if err != nil {
					return errors.WithStack(err)
				}
				table.Def = fmt.Sprintf("CREATE %s %s AS (\n%s\n)", tableType, tableName, strings.TrimRight(tableDef.String, ";"))
			}
		}

As an aside: the same feature request could apply to Oracle and SQL Server, which also support materialized views. I just don't know how they treat these types in their system catalog and if tbls already handles them.

[mjpieters]

I'm very new to Go, and I haven't been able to make this work locally yet, as I can't seem to make Go pass in the tableOid value as an integer, rather than as a string. So currently the above fails with a panic: runtime error: invalid memory address or nil pointer dereference segfault as PostgreSQL can't find the view definition when tableOid is a string rather than an integer.

[mjpieters]

Adding a cast helped:

viewDefRows, err := p.db.Query(`SELECT pg_catalog.pg_get_viewdef($1::oid);`, tableOid)

[k1LoW]

@mjpieters Thank you for your GREAT pull request !

Released as v1.36.0.