PostgreSQL 7.3.5 Programmer's Guide

The PostgreSQL Global Development Group


Table of Contents
Preface
1. What's In This Book
I. Client Interfaces
1. libpq - C Library
1.1. Introduction
1.2. Database Connection Functions
1.3. Command Execution Functions
1.4. Asynchronous Query Processing
1.5. The Fast-Path Interface
1.6. Asynchronous Notification
1.7. Functions Associated with the COPY Command
1.8. libpq Tracing Functions
1.9. libpq Control Functions
1.10. Environment Variables
1.11. Files
1.12. Threading Behavior
1.13. Building Libpq Programs
1.14. Example Programs
2. Large Objects
2.1. Introduction
2.2. Implementation Features
2.3. Interfaces
2.4. Server-side Built-in Functions
2.5. Accessing Large Objects from Libpq
3. pgtcl - Tcl Binding Library
3.1. Introduction
3.2. Loading pgtcl into your application
3.3. pgtcl Command Reference Information
4. ECPG - Embedded SQL in C
4.1. The Concept
4.2. Connecting to the Database Server
4.3. Closing a Connection
4.4. Running SQL Commands
4.5. Passing Data
4.6. Error Handling
4.7. Including Files
4.8. Processing Embedded SQL Programs
4.9. Library Functions
4.10. Porting From Other RDBMS Packages
4.11. For the Developer
5. JDBC Interface
5.1. Setting up the JDBC Driver
5.2. Using the Driver
5.3. Issuing a Query and Processing the Result
5.4. Performing Updates
5.5. Creating and Modifying Database Objects
5.6. Storing Binary Data
5.7. PostgreSQL Extensions to the JDBC API
5.8. Using the driver in a multithreaded or a servlet environment
5.9. Connection Pools And DataSources
5.10. Further Reading
6. PyGreSQL - Python Interface
6.1. The pg Module
6.2. pg Module Functions
6.3. Connection Object: pgobject
6.4. Database Wrapper Class: DB
6.5. Query Result Object: pgqueryobject
6.6. Large Object: pglarge
II. Server Programming
7. Architecture
7.1. PostgreSQL Architectural Concepts
8. Extending SQL: An Overview
8.1. How Extensibility Works
8.2. The PostgreSQL Type System
8.3. About the PostgreSQL System Catalogs
9. Extending SQL: Functions
9.1. Introduction
9.2. Query Language (SQL) Functions
9.3. Procedural Language Functions
9.4. Internal Functions
9.5. C Language Functions
9.6. Function Overloading
9.7. Table Functions
9.8. Procedural Language Handlers
10. Extending SQL: Types
11. Extending SQL: Operators
11.1. Introduction
11.2. Example
11.3. Operator Optimization Information
12. Extending SQL: Aggregates
13. The Rule System
13.1. Introduction
13.2. What is a Query Tree?
13.3. Views and the Rule System
13.4. Rules on INSERT, UPDATE and DELETE
13.5. Rules and Permissions
13.6. Rules and Command Status
13.7. Rules versus Triggers
14. Interfacing Extensions To Indexes
14.1. Introduction
14.2. Access Methods and Operator Classes
14.3. Access Method Strategies
14.4. Access Method Support Routines
14.5. Creating the Operators and Support Routines
14.6. Creating the Operator Class
14.7. Special Features of Operator Classes
15. Index Cost Estimation Functions
16. Triggers
16.1. Trigger Definition
16.2. Interaction with the Trigger Manager
16.3. Visibility of Data Changes
16.4. Examples
17. Server Programming Interface
17.1. Interface Functions
17.2. Interface Support Functions
17.3. Memory Management
17.4. Visibility of Data Changes
17.5. Examples
III. Procedural Languages
18. Procedural Languages
18.1. Introduction
18.2. Installing Procedural Languages
19. PL/pgSQL - SQL Procedural Language
19.1. Overview
19.2. Structure of PL/pgSQL
19.3. Declarations
19.4. Expressions
19.5. Basic Statements
19.6. Control Structures
19.7. Cursors
19.8. Errors and Messages
19.9. Trigger Procedures
19.10. Examples
19.11. Porting from Oracle PL/SQL
20. PL/Tcl - Tcl Procedural Language
20.1. Overview
20.2. Description
21. PL/Perl - Perl Procedural Language
21.1. PL/Perl Functions and Arguments
21.2. Data Values in PL/Perl
21.3. Database Access from PL/Perl
21.4. Trusted and Untrusted PL/Perl
21.5. Missing Features
22. PL/Python - Python Procedural Language
22.1. PL/Python Functions
22.2. Trigger Functions
22.3. Database Access
22.4. Restricted Environment
List of Tables
3-1. pgtcl Commands
5-1. ConnectionPoolDataSource Implementations
5-2. ConnectionPoolDataSource Configuration Properties
5-3. DataSource Implementations
5-4. DataSource Configuration Properties
5-5. Additional Pooling DataSource Configuration Properties
8-1. PostgreSQL System Catalogs
9-1. Equivalent C Types for Built-In PostgreSQL Types
14-1. B-tree Strategies
14-2. Hash Strategies
14-3. R-tree Strategies
14-4. B-tree Support Functions
14-5. Hash Support Functions
14-6. R-tree Support Functions
14-7. GiST Support Functions
19-1. Single Quotes Escaping Chart
List of Figures
7-1. How a connection is established
8-1. The major PostgreSQL system catalogs
List of Examples
1-1. libpq Example Program 1
1-2. libpq Example Program 2
1-3. libpq Example Program 3
2-1. Large Objects with Libpq Example Program
3-1. pgtcl Example Program
5-1. Processing a Simple Query in JDBC
5-2. Simple Delete Example
5-3. Drop Table Example
5-4. Binary Data Examples
5-5. ConnectionPoolDataSource Configuration Example
5-6. DataSource Code Example
5-7. DataSource JNDI Code Example
18-1. Manual Installation of PL/pgSQL
19-1. A PL/pgSQL Trigger Procedure Example
19-2. A Simple PL/pgSQL Function to Increment an Integer
19-3. A Simple PL/pgSQL Function to Concatenate Text
19-4. A PL/pgSQL Function on Composite Type
19-5. A Simple Function
19-6. A Function that Creates Another Function
19-7. A Procedure with a lot of String Manipulation and OUT Parameters