PostgreSQL Programmer's Guide
The PostgreSQL Development Team
Edited by
Thomas Lockhart
PostgreSQL
is Copyright © 1996-9 by the Postgres Global Development Group.
Table of Contents
Summary
1.
Introduction
Resources
Terminology
Notation
Y2K Statement
Copyrights and Trademarks
2.
Architecture
Postgres
Architectural Concepts
3.
Extending
SQL
: An Overview
How Extensibility Works
The
Postgres
Type System
About the
Postgres
System Catalogs
4.
Extending
SQL
: Functions
Query Language (
SQL
) Functions
Examples
SQL
Functions on Base Types
SQL
Functions on Composite Types
Procedural Language Functions
Internal Functions
Compiled (C) Language Functions
C Language Functions on Base Types
C Language Functions on Composite Types
Writing Code
Function Overloading
Name Space Conflicts
5.
Extending
SQL
: Types
User-Defined Types
Functions Needed for a User-Defined Type
Large Objects
6.
Extending
SQL
: Operators
Operator Optimization Information
COMMUTATOR
NEGATOR
RESTRICT
JOIN
HASHES
SORT1 and SORT2
7.
Extending
SQL
: Aggregates
8.
The
Postgres
Rule System
What is a Querytree?
The Parts of a Querytree
Views and the Rule System
Implementation of Views in
Postgres
How SELECT Rules Work
View Rules in Non-SELECT Statements
The Power of Views in
Postgres
Implementation Side Effects
Rules on INSERT, UPDATE and DELETE
Differences to View Rules
How These Rules Work
Cooperation with Views
Rules and Permissions
Rules versus Triggers
9.
Interfacing Extensions To Indices
10.
GiST Indices
11.
Procedural Languages
Installing Procedural Languages
PL/pgSQL
Overview
Description
Examples
PL/Tcl
Overview
Description
12.
Linking Dynamically-Loaded Functions
ULTRIX
DEC OSF/1
SunOS 4.x
,
Solaris 2.x
and
HP-UX
13.
Triggers
Trigger Creation
Interaction with the Trigger Manager
Visibility of Data Changes
Examples
14.
Server Programming Interface
Interface Functions
SPI_connect
SPI_finish
SPI_exec
SPI_prepare
SPI_saveplan
SPI_execp
Interface Support Functions
SPI_copytuple
SPI_modifytuple
SPI_fnumber
SPI_fname
SPI_getvalue
SPI_getbinval
SPI_gettype
SPI_gettypeid
SPI_getrelname
SPI_palloc
SPI_repalloc
SPI_pfree
Memory Management
Visibility of Data Changes
Examples
15.
Large Objects
Historical Note
Inversion Large Objects
Large Object Interfaces
Creating a Large Object
Importing a Large Object
Exporting a Large Object
Opening an Existing Large Object
Writing Data to a Large Object
Seeking on a Large Object
Closing a Large Object Descriptor
Built in registered functions
Accessing Large Objects from LIBPQ
Sample Program
16.
libpq
Database Connection Functions
Query Execution Functions
Asynchronous Query Processing
Fast Path
Asynchronous Notification
Functions Associated with the COPY Command
libpq
Tracing Functions
libpq
Control Functions
User Authentication Functions
Environment Variables
Caveats
Sample Programs
Sample Program 1
Sample Program 2
Sample Program 3
17.
libpq C++ Binding
Control and Initialization
Environment Variables
libpq++ Classes
Connection Class:
PgConnection
Database Class:
PgDatabase
Database Connection Functions
Query Execution Functions
Asynchronous Notification
Functions Associated with the COPY Command
Caveats
18.
pgtcl
Commands
Examples
pgtcl Command Reference Information
pg_connect
pg_disconnect
pg_conndefaults
pg_exec
pg_result
pg_select
pg_listen
pg_lo_creat
pg_lo_open
pg_lo_close
pg_lo_read
pg_lo_write
pg_lo_lseek
pg_lo_tell
pg_lo_unlink
pg_lo_import
pg_lo_export
19.
ecpg
- Embedded
SQL
in
C
Why Embedded
SQL
?
The Concept
How To Use
egpc
Preprocessor
Library
Error handling
Limitations
Porting From Other
RDBMS
Packages
Installation
For the Developer
ToDo List
The Preprocessor
A Complete Example
The Library
20.
ODBC Interface
Background
Windows
Applications
Writing Applications
Unix Installation
Building the Driver
Configuration Files
ApplixWare
Configuration
Common Problems
Debugging ApplixWare ODBC Connections
Running the ApplixWare Demo
Useful Macros
Supported Platforms
21.
JDBC Interface
Building the
JDBC
Interface
Compiling the Driver
Installing the Driver
Preparing the Database for
JDBC
Using the Driver
Importing
JDBC
Loading the Driver
Connecting to the Database
Issuing a Query and Processing the Result
Using the Statement Interface
Using the ResultSet Interface
Performing Updates
Closing the Connection
Using Large Objects
Postgres
Extensions to the
JDBC
API
Further Reading
22.
Lisp Programming Interface
23.
Overview of PostgreSQL Internals
The Path of a Query
How Connections are Established
The Parser Stage
Parser
Transformation Process
The
Postgres
Rule System
The Rewrite System
Planner/Optimizer
Generating Possible Plans
Data Structure of the Plan
Executor
24.
pg_options
25.
Genetic Query Optimization in Database Systems
Query Handling as a Complex Optimization Problem
Genetic Algorithms (
GA
)
Genetic Query Optimization (
GEQO
) in Postgres
Future Implementation Tasks for
Postgres
GEQO
Basic Improvements
References
26.
Frontend/Backend Protocol
Overview
Protocol
Startup
Query
Function Call
Notification Responses
Cancelling Requests in Progress
Termination
Message Data Types
Message Formats
27.
Postgres
Signals
28.
gcc
Default Optimizations
29.
Backend Interface
BKI
File Format
General Commands
Macro Commands
Debugging Commands
Example
30.
Page Files
Page Structure
Files
Bugs
DG1.
The
CVS
Repository
CVS
Tree Organization
Getting The Source Via Anonymous
CVS
Getting The Source Via
CVSup
Preparing A
CVSup
Client System
Running a
CVSup
Client
Installing
CVSup
Installation from Sources
DG2.
Documentation
Documentation Roadmap
The Documentation Project
Documentation Sources
Document Structure
Documentation Files
Document Conversion
Styles and Conventions
SGML Authoring Tools
Building Documentation
Manpages
Hardcopy Generation for v6.5
Text Hardcopy
Postscript Hardcopy
Toolsets
RPM
installation on
Linux
Manual installation of tools
Alternate Toolsets
Bibliography
Next
Summary