+ +
6. EXTENDING SQL: AN OVERVIEW
++ In the sections that follow, we will discuss how you + can extend the POSTGRES SQL query language by adding: +
-
+
- functions +
- types +
- operators +
- aggregates +
+
6.1. How Extensibility Works
+ POSTGRES is extensible because its operation is + catalog-driven. If you are familiar with standard + relational systems, you know that they store information + about databases, tables, columns, etc., in what are + commonly known as system catalogs. (Some systems call + this the data dictionary). The catalogs appear to the + user as classes, like any other, but the DBMS stores + its internal bookkeeping in them. One key difference + between POSTGRES and standard relational systems is + that POSTGRES stores much more information in its + catalogs -- not only information about tables and columns, + but also information about its types, functions, access + methods, and so on. These classes can be modified by + the user, and since POSTGRES bases its internal operation + on these classes, this means that POSTGRES can be + extended by users. By comparison, conventional + database systems can only be extended by changing hardcoded + procedures within the DBMS or by loading modules + specially-written by the DBMS vendor. + POSTGRES is also unlike most other data managers in + that the server can incorporate user-written code into + itself through dynamic loading. That is, the user can + specify an object code file (e.g., a compiled .o file + or shared library) that implements a new type or function + and POSTGRES will load it as required. Code written + in SQL are even more trivial to add to the server. + This ability to modify its operation "on the fly" makes + POSTGRES uniquely suited for rapid prototyping of new + applications and storage structures. + +6.2. The POSTGRES Type System
+ The POSTGRES type system can be broken down in several + ways. + Types are divided into base types and composite types. + Base types are those, likeint4, that are implemented
+ in a language such as C. They generally correspond to
+ what are often known as "abstract data types"; POSTGRES
+ can only operate on such types through methods provided
+ by the user and only understands the behavior of such
+ types to the extent that the user describes them.
+ Composite types are created whenever the user creates a
+ class. EMP is an example of a composite type.
+ POSTGRES stores these types in only one way (within the
+ file that stores all instances of the class) but the
+ user can "look inside" at the attributes of these types
+ from the query language and optimize their retrieval by
+ (for example) defining indices on the attributes.
+ POSTGRES base types are further divided into built-in
+ types and user-defined types. Built-in types (like
+ int4) are those that are compiled into the system.
+ User-defined types are those created by the user in the
+ manner to be described below.
+
+6.3. About the POSTGRES System Catalogs
+ 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 withpg_.
+ The following classes 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.)
+ +
| catalog name | description | +
|---|---|
pg_database | databases | +
pg_class | classes | +
pg_attribute | class attributes | +
pg_index | secondary indices | +
pg_proc | procedures (both C and SQL) | +
pg_type | types (both base and complex) | +
pg_operator | operators | +
pg_aggregate | aggregates and aggregate functions | +
pg_am | access methods | +
pg_amop | access method operators | +
pg_amproc | access method support functions | +
pg_opclass | access method operator classes | +
+ 
+ The Reference Manual gives a more detailed explanation
+ of these catalogs and their attributes. However, Figure 3
+ shows the major entities and their relationships
+ in the system catalogs. (Attributes 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 + attributes used in the queries form foreign keys + in other classes. +
- Many different features (classes, attributes, + functions, types, access methods, etc.) are + tightly integrated in this schema. A simple + create command may modify many of these catalogs. +
- Types and procedures [6] + are central to the schema. + Nearly every catalog contains some reference to + instances in one or both of these classes. For + example, POSTGRES frequently uses type + signatures (e.g., of functions and operators) to + identify unique instances of other catalogs. +
- There are many attributes and relationships that
+ have obvious meanings, but there are many
+ (particularly those that have to do with access
+ methods) that do not. The relationships between
+
pg_am, pg_amop, pg_amproc, pg_operatorand +pg_opclassare particularly hard to understand + and will be described in depth (in the section + on interfacing types and operators to indices) + after we have discussed basic extensions. +
+
+6. We use the words procedure and function more or less +interchangably. +
+ +[ TOC ] +[ Previous ] +[ Next ] + +