Chapter 14. Advanced PostgreSQL Programming

I l @ ve RuBoard

One of the true benefits PostgreSQL has over many commercial RDBMSs is that it can be regularly extended. For instance, PostgreSQL does not natively have a data type for a dewey -decimal object. However, if you were creating a database that was going to serve as the back-end system to a large library that used the Dewey Decimal system, that could be a very useful data type.

Plugging in new features and objects into the database is known as "extending" it. Fundamentally, PostgreSQL enables this by allowing users to write new C-based objects and by using the resultant function as a handler for specific data type, operator, or aggregate needs.

The basic act of extending PostgreSQL involves the following steps:

1. Creating a C-based shared object that performs the function desired.

2. Registering that function with the PostgreSQL back end through the use of the CREATE FUNCTION command.

3. Linking the proper SQL command (for example, CREATE TYPE , CREATE OPERATOR , and so on) with that registered object.

Understanding how extensibility works first requires an overview of the PostgreSQL catalog system. The system catalogs are essentially just special tables. However, instead of storing user data, these tables store information regarding how operators, functions, data types, aggregates, rules, and triggers are defined. Therefore, by using the provided mechanisms to modify these tables, the PostgreSQL system itself can be extended.

One type of information that these tables store is pointers to compiled shared objects that handle specific database functions. In essence, the CREATE FUNCTION , CREATE OPERATOR , CREATE TYPE , and CREATE AGGREGATE commands modify these system catalogs to include definitions for this extra functionality.

The basic breakdown of system catalogs can be defined as shown in Table 14.1.

Table 14.1. System Catalogs

Table	Description
pg_aggregate	Aggregates and aggregate functions
pg_am	Access methods
pg_amop	Access method operators
pg_amproc	Access method support functions
pg_attribute	Table columns
pg_class	Tables
pg_database	Databases
pg_index	Secondary indices
pg_opclass	Access method operator classes
pg_operator	Operators
pg_proc	Procedures (both C and SQL)
pg_type	Types (both base and complex)

I l @ ve RuBoard