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. This part also describes the server-side programming languages available in the PostgreSQL distribution as well as general issues concerning server-side programming languages. This information is only useful to readers that have read at least the first few chapters of this part.

Table of Contents

31. Extending SQL
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. Procedural Language Functions
31.6. Internal Functions
31.7. C-Language Functions
31.7.1. Dynamic Loading
31.7.2. Base Types in C-Language Functions
31.7.3. Calling Conventions Version 0 for C-Language Functions
31.7.4. Calling Conventions Version 1 for C-Language Functions
31.7.5. Writing Code
31.7.6. Compiling and Linking Dynamically-Loaded Functions
31.7.7. Extension build infrastructure
31.7.8. Composite-Type Arguments in C-Language Functions
31.7.9. Returning Rows (Composite Types) from C-Language Functions
31.7.10. Returning Sets from C-Language Functions
31.7.11. Polymorphic Arguments and Return Types
31.8. Function Overloading
31.9. Function Volatility Categories
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
32. The Rule System
32.1. The Query Tree
32.2. Views and the Rule System
32.2.1. How SELECT Rules Work
32.2.2. View Rules in Non-SELECT Statements
32.2.3. The Power of Views in PostgreSQL
32.2.4. Updating a View
32.3. Rules on INSERT, UPDATE, and DELETE
32.3.1. How Update Rules Work
32.3.2. Cooperation with Views
32.4. Rules and Privileges
32.5. Rules and Command Status
32.6. Rules versus Triggers
33. Triggers
33.1. Overview of Trigger Behavior
33.2. Visibility of Data Changes
33.3. Writing Trigger Functions in C
33.4. A Complete Example
34. Procedural Languages
34.1. Installing Procedural Languages
35. PL/pgSQL - SQL Procedural Language
35.1. Overview
35.1.1. Advantages of Using PL/pgSQL
35.1.2. Supported Argument and Result Data Types
35.2. Tips for Developing in PL/pgSQL
35.2.1. Handling of Quotation Marks
35.3. Structure of PL/pgSQL
35.4. Declarations
35.4.1. Aliases for Function Parameters
35.4.2. Copying Types
35.4.3. Row Types
35.4.4. Record Types
35.4.5. RENAME
35.5. Expressions
35.6. Basic Statements
35.6.1. Assignment
35.6.2. SELECT INTO
35.6.3. Executing an Expression or Query With No Result
35.6.4. Doing Nothing At All
35.6.5. Executing Dynamic Commands
35.6.6. Obtaining the Result Status
35.7. Control Structures
35.7.1. Returning From a Function
35.7.2. Conditionals
35.7.3. Simple Loops
35.7.4. Looping Through Query Results
35.7.5. Trapping Errors
35.8. Cursors
35.8.1. Declaring Cursor Variables
35.8.2. Opening Cursors
35.8.3. Using Cursors
35.9. Errors and Messages
35.10. Trigger Procedures
35.11. Porting from Oracle PL/SQL
35.11.1. Porting Examples
35.11.2. Other Things to Watch For
35.11.3. Appendix
36. PL/Tcl - Tcl Procedural Language
36.1. Overview
36.2. PL/Tcl Functions and Arguments
36.3. Data Values in PL/Tcl
36.4. Global Data in PL/Tcl
36.5. Database Access from PL/Tcl
36.6. Trigger Procedures in PL/Tcl
36.7. Modules and the unknown command
36.8. Tcl Procedure Names
37. PL/Perl - Perl Procedural Language
37.1. PL/Perl Functions and Arguments
37.2. Database Access from PL/Perl
37.3. Data Values in PL/Perl
37.4. Global Values in PL/Perl
37.5. Trusted and Untrusted PL/Perl
37.6. PL/Perl Triggers
37.7. Limitations and Missing Features
38. PL/Python - Python Procedural Language
38.1. PL/Python Functions
38.2. Trigger Functions
38.3. Database Access
39. Server Programming Interface
39.1. Interface Functions
39.2. Interface Support Functions
39.3. Memory Management
39.4. Visibility of Data Changes
39.5. Examples