Supported Versions: Current (16) / 15 / 14 / 13 / 12
Development Versions: devel
Unsupported versions: 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.

Chapter 33. Extending SQL

Table of Contents
33.1. How Extensibility Works
33.2. The PostgreSQL Type System
33.2.1. Base Types
33.2.2. Composite Types
33.2.3. Domains
33.2.4. Pseudo-Types
33.2.5. Polymorphic Types
33.3. User-Defined Functions
33.4. Query Language (SQL) Functions
33.4.1. SQL Functions on Base Types
33.4.2. SQL Functions on Composite Types
33.4.3. SQL Functions as Table Sources
33.4.4. SQL Functions Returning Sets
33.4.5. Polymorphic SQL Functions
33.5. Procedural Language Functions
33.6. Internal Functions
33.7. C-Language Functions
33.7.1. Dynamic Loading
33.7.2. Base Types in C-Language Functions
33.7.3. Calling Conventions Version 0 for C-Language Functions
33.7.4. Calling Conventions Version 1 for C-Language Functions
33.7.5. Writing Code
33.7.6. Compiling and Linking Dynamically-Loaded Functions
33.7.7. Composite-Type Arguments in C-Language Functions
33.7.8. Returning Rows (Composite Types) from C-Language Functions
33.7.9. Returning Sets from C-Language Functions
33.7.10. Polymorphic Arguments and Return Types
33.8. Function Overloading
33.9. User-Defined Aggregates
33.10. User-Defined Types
33.11. User-Defined Operators
33.12. Operator Optimization Information
33.12.1. COMMUTATOR
33.12.2. NEGATOR
33.12.3. RESTRICT
33.12.4. JOIN
33.12.5. HASHES
33.12.6. MERGES (SORT1, SORT2, LTCMP, GTCMP)
33.13. Interfacing Extensions To Indexes
33.13.1. Index Methods and Operator Classes
33.13.2. Index Method Strategies
33.13.3. Index Method Support Routines
33.13.4. An Example
33.13.5. System Dependencies on Operator Classes
33.13.6. Special Features of Operator Classes

In the sections that follow, we will discuss how you can extend the PostgreSQL SQL query language by adding:

33.1. How Extensibility Works

PostgreSQL is extensible because its operation is catalog-driven. If you are familiar with standard relational database 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 tables like any other, but the DBMS stores its internal bookkeeping in them. One key difference between PostgreSQL and standard relational database systems is that PostgreSQL stores much more information in its catalogs: not only information about tables and columns, but also information about data types, functions, access methods, and so on. These tables can be modified by the user, and since PostgreSQL bases its 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 in the source code or by loading modules specially written by the DBMS vendor.

The PostgreSQL server can moreover incorporate user-written code into itself through dynamic loading. That is, the user can specify an object code file (e.g., a 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.