September 26, 2024: PostgreSQL 17 Released!
Supported Versions: Current (17) / 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 31. Extending SQL

Table of Contents
31.1. How Extensibility Works
31.2. The PostgreSQL Type System
31.2.1. Base Types
31.2.2. Composite Types
31.2.3. Domains
31.2.4. Pseudo-Types
31.2.5. Polymorphic Types
31.3. User-Defined Functions
31.4. Query Language (SQL) Functions
31.4.1. SQL Functions on Base Types
31.4.2. SQL Functions on Composite Types
31.4.3. SQL Functions as Table Sources
31.4.4. SQL Functions Returning Sets
31.4.5. Polymorphic SQL Functions
31.5. Function Overloading
31.6. Function Volatility Categories
31.7. Procedural Language Functions
31.8. Internal Functions
31.9. C-Language Functions
31.9.1. Dynamic Loading
31.9.2. Base Types in C-Language Functions
31.9.3. Calling Conventions Version 0 for C-Language Functions
31.9.4. Calling Conventions Version 1 for C-Language Functions
31.9.5. Writing Code
31.9.6. Compiling and Linking Dynamically-Loaded Functions
31.9.7. Extension Building Infrastructure
31.9.8. Composite-Type Arguments in C-Language Functions
31.9.9. Returning Rows (Composite Types) from C-Language Functions
31.9.10. Returning Sets from C-Language Functions
31.9.11. Polymorphic Arguments and Return Types
31.10. User-Defined Aggregates
31.11. User-Defined Types
31.12. User-Defined Operators
31.13. Operator Optimization Information
31.13.1. COMMUTATOR
31.13.2. NEGATOR
31.13.3. RESTRICT
31.13.4. JOIN
31.13.5. HASHES
31.13.6. MERGES (SORT1, SORT2, LTCMP, GTCMP)
31.14. Interfacing Extensions To Indexes
31.14.1. Index Methods and Operator Classes
31.14.2. Index Method Strategies
31.14.3. Index Method Support Routines
31.14.4. An Example
31.14.5. Cross-Data-Type Operator Classes
31.14.6. System Dependencies on Operator Classes
31.14.7. Special Features of Operator Classes

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

31.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.