Postgres materialized views not listed

[ludcila]

I am using version 6.0.0-rc2 to generate the documentation of a Postgres database. It works well, however, the only problem I have is that it is not showing any of the materialized views. Any ideas?

[npetzall]

Hi, could you download and try with snapshot version. Link to download can be found in readme/overview.

What commandline arguments are you using? (remember to remove password)

[npetzall]

Which version of Postgres are you using? Just so that I can recreate the issue.

There are a couple of possibilities so I would like to recreate the issue.

[ludcila]

I am using Postgres 9.6. This is how I am running schema spy:

java -jar schema-spy/schemaspy-6.0.0-rc2.jar -t pgsql -host %server% -port %port% -db %database% -u %user% -p %password% -all -o docs -dp schema-spy/postgresql-42.1.1.jar

I haven't tried with the snapshot version yet.

[npetzall]

No need to test with snapshot, it won't work.

I've found two issue, I'm gonna submit a PR. But there might be some open questions on the changes.

Should Materialized views be grouped with views, how/if they should be marked. Since it's Postgres specific.

[ludcila]

I see. Thanks @npetzall

[npetzall]

As a quick "fix" this will just add them to view without any way to differentiate between materialize and non-materialized.

Create a file i same folder as SchemaSpy jar-file named pgsql-mat.properties It should have the following content:

#
# see http://schemaspy.org/dbtypes.html
# for configuration / customization details
#

description=PostgreSQL
connectionSpec=jdbc:postgresql://<hostOptionalPort>/<db>
host=host where database resides with optional port
port=port database is listening on
db=database name

driver=org.postgresql.Driver

# Sample path to the postgresql drivers.
# Use -dp to override.
driverPath=/org/schemaspy/drivers/postgresql-9.4.1208.jre6.jar

# return text that represents a specific :view / :schema
selectViewSql=select definition as view_definition from pg_views where viewname = :table UNION select definition as view_definition from pg_matviews where matviewname = :table

selectSchemasSql=SELECT nspname AS schema_name, pg_catalog.obj_description(oid, 'pg_namespace') AS schema_comment FROM pg_catalog.pg_namespace where nspname = :schema
selectCatalogsSql=SELECT datname AS catalog_name, pg_catalog.obj_description(datlastsysoid, 'pg_database') AS catalog_comment FROM pg_catalog.pg_database WHERE datname = :catalog ;
# selectTablesSql=select table_schema as table_catalog, null as table_schema, table_name, table_comment, table_rows from information_schema.tables where table_schema=:schema and table_type='BASE TABLE'

viewTypes=VIEW,MATERIALIZED VIEW

Now just replace -t pgsql with -t pgsql-mat

[ludcila]

That works too. Thanks!

[atsaloli]

We use materialized views in our Postgres database that we want to document with SchemaSpy. It would be nice if materialized views were included in the output. Thanks! :)

[npetzall]

@ludcila @atsaloli Materialized views are added to snapshot. However they are not marked as materialized. So they show as views.

[ludcila]

Thanks @npetzall, I am good with that. I actually followed your suggestion of adding the pgsql-mat.properties file. That worked for me :)

[atsaloli]

Hi @npetzall I'm good with that too! I just tried 6.0.1-snapshot and it's output lists the materialized views; whereas 6.0.0 omitted materialized views. Thank you. :)

[npetzall]

Everyone seems happy with materialized views included in normal views so we close this.

We can always re-open or create new issue if some kind of distinction between them is needed.

[bminahan73]

What if a schema only has materialized views and no tables (tables in another schema)? It would be great to be able to output this as well. Right now the workaround is to output both the schema with the tables and the schema with the materialized views. If I just try to output the schema with materialized views I get an error that it is an empty schema.

[npetzall]

@bminahan73 it sounds like a new issue. Since the schema shouldn't be considered empty, it has views. So create a new issue. If you could run with -debug and attache the error output it would help me a lot, don't forget to mask any sensitive information.

[carlosdenner]

Has this solution for views reached the new version? I am not getting any metadata, relationships, indexes, etc. from the views, only from the tables. Appreciate any help.

[npetzall]

This is in 6.1.0, but as my last comment they just show up amongst the views. So the information is limited.

[carlosdenner]

Thanks, so how/what can I see of a postgresql View?

On Wed, Nov 25, 2020 at 11:48 AM Nils Petzäll notifications@github.com wrote:

This is in 6.1.0, but as my last comment they just show up amongst the views. So the information is limited.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/schemaspy/schemaspy/issues/387#issuecomment-733823908, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAUXAF3CLOHGRY6L4AMQBSTSRUYOFANCNFSM4FMWPLQA .

-- Carlos Denner dos Santos, PhD

[npetzall]

http://schemaspy.org/sample/tables/AlbumsArtists.html Something like that, this was a long time ago, so I'm not super sure if materialized view are almost tables or not.

If you want more data, you could request an enhancement. In that case we could see if they share some properties with tables.