Part V. Server Programming

This part is about extending the server functionality with user-defined functions, data types, triggers, etc. These are advanced topics which should probably be approached only after all the other user documentation about PostgreSQL has been understood. Later chapters in this part describe the server-side programming languages available in the PostgreSQL distribution as well as general issues concerning server-side programming languages. It is essential to read at least the earlier sections of Chapter 33, Extending SQL (covering functions) before diving into the material about server-side programming languages.

Table of Contents

33. Extending SQL
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. Functions with Output Parameters
33.4.4. SQL Functions as Table Sources
33.4.5. SQL Functions Returning Sets
33.4.6. Polymorphic SQL Functions
33.5. Function Overloading
33.6. Function Volatility Categories
33.7. Procedural Language Functions
33.8. Internal Functions
33.9. C-Language Functions
33.9.1. Dynamic Loading
33.9.2. Base Types in C-Language Functions
33.9.3. Version 0 Calling Conventions
33.9.4. Version 1 Calling Conventions
33.9.5. Writing Code
33.9.6. Compiling and Linking Dynamically-Loaded Functions
33.9.7. Extension Building Infrastructure
33.9.8. Composite-Type Arguments
33.9.9. Returning Rows (Composite Types)
33.9.10. Returning Sets
33.9.11. Polymorphic Arguments and Return Types
33.9.12. Shared Memory and LWLocks
33.10. User-Defined Aggregates
33.11. User-Defined Types
33.12. User-Defined Operators
33.13. Operator Optimization Information
33.13.1. COMMUTATOR
33.13.2. NEGATOR
33.13.3. RESTRICT
33.13.4. JOIN
33.13.5. HASHES
33.13.6. MERGES (SORT1, SORT2, LTCMP, GTCMP)
33.14. Interfacing Extensions To Indexes
33.14.1. Index Methods and Operator Classes
33.14.2. Index Method Strategies
33.14.3. Index Method Support Routines
33.14.4. An Example
33.14.5. Cross-Data-Type Operator Classes
33.14.6. System Dependencies on Operator Classes
33.14.7. Special Features of Operator Classes
34. Triggers
34.1. Overview of Trigger Behavior
34.2. Visibility of Data Changes
34.3. Writing Trigger Functions in C
34.4. A Complete Example
35. The Rule System
35.1. The Query Tree
35.2. Views and the Rule System
35.2.1. How SELECT Rules Work
35.2.2. View Rules in Non-SELECT Statements
35.2.3. The Power of Views in PostgreSQL
35.2.4. Updating a View
35.3. Rules on INSERT, UPDATE, and DELETE
35.3.1. How Update Rules Work
35.3.2. Cooperation with Views
35.4. Rules and Privileges
35.5. Rules and Command Status
35.6. Rules versus Triggers
36. Procedural Languages
36.1. Installing Procedural Languages
37. PL/pgSQL - SQL Procedural Language
37.1. Overview
37.1.1. Advantages of Using PL/pgSQL
37.1.2. Supported Argument and Result Data Types
37.2. Tips for Developing in PL/pgSQL
37.2.1. Handling of Quotation Marks
37.3. Structure of PL/pgSQL
37.4. Declarations
37.4.1. Aliases for Function Parameters
37.4.2. Copying Types
37.4.3. Row Types
37.4.4. Record Types
37.4.5. RENAME
37.5. Expressions
37.6. Basic Statements
37.6.1. Assignment
37.6.2. Executing a Query With No Result
37.6.3. Executing a Query with a Single-Row Result
37.6.4. Doing Nothing At All
37.6.5. Executing Dynamic Commands
37.6.6. Obtaining the Result Status
37.7. Control Structures
37.7.1. Returning From a Function
37.7.2. Conditionals
37.7.3. Simple Loops
37.7.4. Looping Through Query Results
37.7.5. Trapping Errors
37.8. Cursors
37.8.1. Declaring Cursor Variables
37.8.2. Opening Cursors
37.8.3. Using Cursors
37.9. Errors and Messages
37.10. Trigger Procedures
37.11. Porting from Oracle PL/SQL
37.11.1. Porting Examples
37.11.2. Other Things to Watch For
37.11.3. Appendix
38. PL/Tcl - Tcl Procedural Language
38.1. Overview
38.2. PL/Tcl Functions and Arguments
38.3. Data Values in PL/Tcl
38.4. Global Data in PL/Tcl
38.5. Database Access from PL/Tcl
38.6. Trigger Procedures in PL/Tcl
38.7. Modules and the unknown command
38.8. Tcl Procedure Names
39. PL/Perl - Perl Procedural Language
39.1. PL/Perl Functions and Arguments
39.2. Database Access from PL/Perl
39.3. Data Values in PL/Perl
39.4. Global Values in PL/Perl
39.5. Trusted and Untrusted PL/Perl
39.6. PL/Perl Triggers
39.7. Limitations and Missing Features
40. PL/Python - Python Procedural Language
40.1. PL/Python Functions
40.2. Trigger Functions
40.3. Database Access
41. Server Programming Interface
41.1. Interface Functions
41.2. Interface Support Functions
41.3. Memory Management
41.4. Visibility of Data Changes
41.5. Examples