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 32, Extending SQL (covering functions) before diving into the material about server-side programming languages.

Table of Contents

32. Extending SQL
32.1. How Extensibility Works
32.2. The PostgreSQL Type System
32.2.1. Base Types
32.2.2. Composite Types
32.2.3. Domains
32.2.4. Pseudo-Types
32.2.5. Polymorphic Types
32.3. User-Defined Functions
32.4. Query Language (SQL) Functions
32.4.1. SQL Functions on Base Types
32.4.2. SQL Functions on Composite Types
32.4.3. Functions with Output Parameters
32.4.4. SQL Functions as Table Sources
32.4.5. SQL Functions Returning Sets
32.4.6. Polymorphic SQL Functions
32.5. Function Overloading
32.6. Function Volatility Categories
32.7. Procedural Language Functions
32.8. Internal Functions
32.9. C-Language Functions
32.9.1. Dynamic Loading
32.9.2. Base Types in C-Language Functions
32.9.3. Calling Conventions Version 0 for C-Language Functions
32.9.4. Calling Conventions Version 1 for C-Language Functions
32.9.5. Writing Code
32.9.6. Compiling and Linking Dynamically-Loaded Functions
32.9.7. Extension Building Infrastructure
32.9.8. Composite-Type Arguments in C-Language Functions
32.9.9. Returning Rows (Composite Types) from C-Language Functions
32.9.10. Returning Sets from C-Language Functions
32.9.11. Polymorphic Arguments and Return Types
32.10. User-Defined Aggregates
32.11. User-Defined Types
32.12. User-Defined Operators
32.13. Operator Optimization Information
32.13.1. COMMUTATOR
32.13.2. NEGATOR
32.13.3. RESTRICT
32.13.4. JOIN
32.13.5. HASHES
32.13.6. MERGES (SORT1, SORT2, LTCMP, GTCMP)
32.14. Interfacing Extensions To Indexes
32.14.1. Index Methods and Operator Classes
32.14.2. Index Method Strategies
32.14.3. Index Method Support Routines
32.14.4. An Example
32.14.5. Cross-Data-Type Operator Classes
32.14.6. System Dependencies on Operator Classes
32.14.7. Special Features of Operator Classes
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. The Rule System
34.1. The Query Tree
34.2. Views and the Rule System
34.2.1. How SELECT Rules Work
34.2.2. View Rules in Non-SELECT Statements
34.2.3. The Power of Views in PostgreSQL
34.2.4. Updating a View
34.3. Rules on INSERT, UPDATE, and DELETE
34.3.1. How Update Rules Work
34.3.2. Cooperation with Views
34.4. Rules and Privileges
34.5. Rules and Command Status
34.6. Rules versus Triggers
35. Procedural Languages
35.1. Installing Procedural Languages
36. PL/pgSQL - SQL Procedural Language
36.1. Overview
36.1.1. Advantages of Using PL/pgSQL
36.1.2. Supported Argument and Result Data Types
36.2. Tips for Developing in PL/pgSQL
36.2.1. Handling of Quotation Marks
36.3. Structure of PL/pgSQL
36.4. Declarations
36.4.1. Aliases for Function Parameters
36.4.2. Copying Types
36.4.3. Row Types
36.4.4. Record Types
36.4.5. RENAME
36.5. Expressions
36.6. Basic Statements
36.6.1. Assignment
36.6.2. SELECT INTO
36.6.3. Executing an Expression or Query With No Result
36.6.4. Doing Nothing At All
36.6.5. Executing Dynamic Commands
36.6.6. Obtaining the Result Status
36.7. Control Structures
36.7.1. Returning From a Function
36.7.2. Conditionals
36.7.3. Simple Loops
36.7.4. Looping Through Query Results
36.7.5. Trapping Errors
36.8. Cursors
36.8.1. Declaring Cursor Variables
36.8.2. Opening Cursors
36.8.3. Using Cursors
36.9. Errors and Messages
36.10. Trigger Procedures
36.11. Porting from Oracle PL/SQL
36.11.1. Porting Examples
36.11.2. Other Things to Watch For
36.11.3. Appendix
37. PL/Tcl - Tcl Procedural Language
37.1. Overview
37.2. PL/Tcl Functions and Arguments
37.3. Data Values in PL/Tcl
37.4. Global Data in PL/Tcl
37.5. Database Access from PL/Tcl
37.6. Trigger Procedures in PL/Tcl
37.7. Modules and the unknown command
37.8. Tcl Procedure Names
38. PL/Perl - Perl Procedural Language
38.1. PL/Perl Functions and Arguments
38.2. Database Access from PL/Perl
38.3. Data Values in PL/Perl
38.4. Global Values in PL/Perl
38.5. Trusted and Untrusted PL/Perl
38.6. PL/Perl Triggers
38.7. Limitations and Missing Features
39. PL/Python - Python Procedural Language
39.1. PL/Python Functions
39.2. Trigger Functions
39.3. Database Access
40. Server Programming Interface
40.1. Interface Functions
40.2. Interface Support Functions
40.3. Memory Management
40.4. Visibility of Data Changes
40.5. Examples