Having introduced the basic extensibility concepts, we
can now take a look at how the catalogs are actually
laid out. You can skip this section for now, but some
later sections will be incomprehensible without the
information given here, so mark this page for later
reference.
All system catalogs have names that begin with
pg_.
The following tables contain information that may be
useful to the end user. (There are many other system
catalogs, but there should rarely be a reason to query
them directly.)
Table 8-1. PostgreSQL System Catalogs
| Catalog Name | Description |
|---|
| pg_database | databases |
| pg_class | tables |
| pg_attribute | table columns |
| pg_index | indexes |
| pg_proc | procedures/functions |
| pg_type | data types (both base and complex) |
| pg_operator | operators |
| pg_aggregate | aggregate functions |
| pg_am | access methods |
| pg_amop | access method operators |
| pg_amproc | access method support functions |
| pg_opclass | access method operator classes |
The
Developer's Guide gives a more detailed explanation
of these catalogs and their columns. However,
Figure 8-1
shows the major entities and their relationships
in the system catalogs. (Columns that do not refer
to other entities are not shown unless they are part of
a primary key.)
This diagram is more or less incomprehensible until you
actually start looking at the contents of the catalogs
and see how they relate to each other. For now, the
main things to take away from this diagram are as follows:
In several of the sections that follow, we will
present various join queries on the system
catalogs that display information we need to extend
the system. Looking at this diagram should make
some of these join queries (which are often
three- or four-way joins) more understandable,
because you will be able to see that the
columns used in the queries form foreign keys
in other tables.
Many different features (tables, columns,
functions, types, access methods, etc.) are
tightly integrated in this schema. A simple
create command may modify many of these catalogs.
Types and procedures
are central to the schema.
Note: We use the words procedure
and function more or less interchangeably.
Nearly every catalog contains some reference to
rows in one or both of these tables. For
example, PostgreSQL frequently uses type
signatures (e.g., of functions and operators) to
identify unique rows of other catalogs.
There are many columns and relationships that
have obvious meanings, but there are many
(particularly those that have to do with access
methods) that do not.
