id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
29722	Add introspection of special table and view types.	Nick Pope	Nick Pope	"**Originally this ticket was aimed at PostgreSQL:**

Looking at the documentation for [https://www.postgresql.org/docs/10/static/catalog-pg-class.html pg_class] there are the following types that we might be interested in introspecting for generation of models:

- `r` — ordinary table — obviously this is already supported.
- `v` — views — support was implemented in #29004.
- `f` — foreign table — being addressed by #29719.
- `m` — materialized views — could be handled much like #29004, mapping `m` to `v`.
- `p` — partitioned table — could be handled mapping `p` to `t`, but gets more complicated, read on below...

For partitioned tables, we are unlikely to want to generate models for all of the individual partitions, although this could be supported with an `--include-partitions` flag.

From [https://paquier.xyz/postgresql-2/partition-information/ this link] we can see an example of output for partitioned tables:

{{{
 relid |      relname       | relsize | relispartition | relkind
-------+--------------------+---------+----------------+---------
 16410 | population         |       0 | f              | p
 16417 | population_s       |    8192 | t              | r
 16424 | population_t       |       0 | t              | p
 16431 | population_t_10_20 |    8192 | t              | r
 16445 | population_t_20_30 |    8192 | t              | r
}}}

On PostgreSQL 10+ we need to filter on `NOT relispartition` to ignore all of the partitions and only include the parent (which is the table that is usually interacted with).

**Regarding support for all backends:**

Feature support by backend:

||= =||= PostgreSQL =||= MySQL =||= Oracle =||= SQLite =||
|| Partitions || ✔ || ✔ || ✔ || ✘ ||
|| Foreign Tables || ✔ || ✘ || ✘ || ✘ ||
|| Materialized Views || ✔ || ✘ || ✔ || ✘ ||

It looks partitions can be introspected using `INFORMATION_SCHEMA.PARTITIONS` for [https://dev.mysql.com/doc/refman/8.0/en/partitions-table.html MySQL] and there are other options for [https://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin005.htm Oracle].

For introspection of materialized views, I think we could use `USER_MVIEWS` for [https://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_5332.htm#REFRN26139 Oracle]."	New feature	closed	Database layer (models, ORM)	dev	Normal	fixed	postgresql, introspection, inspectdb, views, partitions		Accepted	0	0	0	0	0	0