Is the mapping between PostgreSQL system tables and information_schema views documented?

Question

If you connect pgadminIII to a PostgreSQL 9.x server, you'll find two catalogs exposed in the user interface: ANSI (information schema), and PostgreSQL (pg_catalog).

I'm looking for documentation that describes the mapping between

• database objects and the system tables
• the system tables and the information_schema views

For example, I'd like to be able to take a sequence name from a database, and be able to tell quickly where the sequence itself, the sequence's name, and the sequence's ACL (from GRANT and REVOKE statements) would be found in both the system tables and in the information_schema views.

Is that documented anywhere? I've been through PostgreSQL's online documentation, and I've found details of both the ANSI and PostgreSQL catalogs, but I haven't found documentation of the mappings.

Update

It looks like the information is probably in the source file information_schema.sql, but that file doesn't seem to be in the online source code. (See, for example, initdb.c at http://doxygen.postgresql.org/.)

Answer 1

The system tables are the implementation of database objects. So if you, say, call a function foo(), the DBMS looks in pg_proc to see if there is a function foo and what the arguments and the source code and so on are. The layout and arrangement of the system catalogs are merely the way the implementors of various features over time made them. You already pointed out the documentation of the catalogs. In many cases, there is a simple mapping, say, between a function and pg_proc. But in other cases, such as for an index, it's a bit more complicated. You will have to dig that information out of the documentation or perhaps the many examples of system catalog queries flying around.

The information schema is specified by the SQL standard. The principle there is, if you enter these DDL commands, then a query of the information schema should give these results. In many cases, there is again a simple mapping between objects and information schema views, but it's not straightforward in all cases. So maintaining a separate documentation of the mapping of this information would be cumbersome and probably useless. The principle is DDL in, information schema out, not what happens in the PostgreSQL system catalogs.

Ultimately, if you want to know whether a sequence would be found in a catalog table or an information schema view, you need to query these tables. The reality is too complicated for this to be simpler. (I think. Send a patch if you have a better idea.)

Answer 2

Since most of the information schema is in the form of views against pg_catalog you can get a big chunk of it using psql:

psql -U postgres -c "\d information_schema.*" > information_schema.sql