In the sections that follow, we will discuss how you can extend the PostgreSQL SQL query language by adding:
Types
Functions
Operators
Aggregates.
As it turns out, part of defining a new type is the definition of functions that describe its behavior. Consequently, while it is possible to define a new function without defining a new type, the reverse is not true. We therefore describe how to add new functions to PostgreSQL before describing how to add new types.
In this section we explore how catalogs make PostgreSQL extensible and how the PostgreSQL type system works.
PostgreSQL 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 refer to this as the data dictionary). The catalogs appear to the user as tables like any other, but the DBMS stores its internal bookkeeping in them. One key difference between PostgreSQL and standard relational systems is that PostgreSQL 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 tables can be modified by the user, and since PostgreSQL bases its internal operation on these tables, this means that PostgreSQL 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.
PostgreSQL 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 (for example, a compiled .o file or shared library) that implements a new type or function and PostgreSQL will load it as required. Code written in SQL is even more trivial to add to the server. This ability to modify its operation "on the fly" makes PostgreSQL uniquely suited for rapid prototyping of new applications and storage structures.
The PostgreSQL type system can be broken down in several ways. For instance, types can be divided into base types and composite types. Base types are those, like int4, that are implemented in a language such as C. They generally correspond to what are often known as abstract data types; PostgreSQL can operate on such types only through methods provided by the user and understands the behavior of such types only to the extent that the user describes them. Composite types are created whenever the user creates a table and describes a row of that table. Composite types do not need any function defined on them because the system already understands what they look like inside.
PostgreSQL stores these types in only one way (within the file that stores all rows of a table) but the user can "look inside" at the attributes of these types from the query language and optimize their retrieval by (for example) defining indexes on the attributes.
PostgreSQL base types can be 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 later.