materialized views in dbListTables()

[dpprdan]

Should Materialized Views be listed by dbListTables()?

I'd argue they should since they are objects that share characteristics of both Tables and Views, both of which are returned by dbListTables(), see #27 & #29.

See this thread on the psql-hackers list for a discussion on why they are not in the INFORMATION_SCHEMA.tables (which includes Views). Tl;dr: "They are not defined by the SQL standard." I see this as a relatively minor point w.r.t. RPostgres, though.

As it stands there is no helper function like dbListTables() to list Materialized Views in RPostgres.

The following query shows all Tables, Views and Materialized Views (I have not looked into temporary tables, yet):

SELECT tablename AS name FROM pg_tables WHERE schemaname = ANY (current_schemas(false))
UNION
SELECT viewname AS name FROM pg_views WHERE schemaname = ANY (current_schemas(false))
UNION
SELECT matviewname as name FROM pg_matviews WHERE schemaname = ANY (current_schemas(false))
ORDER BY name;

[dpprdan]

Probably a better implementation and one that is closer to the current one:

SELECT c.relname AS name
FROM   pg_class AS c 
  JOIN pg_namespace AS n 
  ON   c.relnamespace = n.oid
WHERE  (n.nspname = ANY (current_schemas(true)))
  AND  (n.nspname <> 'pg_catalog')
  AND  (relkind IN ('r', 'p', 'f', 'v', 'm'))
  AND NOT relispartition
ORDER BY name

This also includes foreign and partitioned tables like INFORMATION_SCHEMA.tables (pg_class documentation).

AND NOT relispartition omits the partitions of a parrtitioned table. Tables with many partitions would otherwise clutter the list and I don't see a use-case for accessing the partitions directly from R. I might be missing something, though.

[krlmlr]

Thanks. This sounds like a useful extension. Would you like to submit a pull request?

[dpprdan]

I submitted a PR for dbListTables() (#261), but now realise that this probably should extend to dbExistsTable() and dbListFields() as well?! Update: And also dbListObjects().

I assume that find_table(), which currently uses INFORMATION_SCHEMA, would have to be adjusted, but I have not fully grasped how that function works, yet.

[krlmlr]

Thanks for the PR. Yes, we should support all methods.

find_table() composes a query against INFORMATION_SCHEMA. We need to determine where and how materialized views are stored in INFORMATION_SCHEMA, and adjust accordingly.

[krlmlr]

If you prefer staying in line with your pull request and use Postgres-specific internal views, we need to rewrite so that all use cases of find_table() are covered.

[krlmlr]

Comment by @jaredlander: https://twitter.com/jaredlander/status/1410673131686084614.

[krlmlr]

Ancient dplyr issue: tidyverse/dplyr#1007.