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

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

SQL Functions on Base Types
SQL Functions on Composite Types

Programming Language Functions

Programming Language Functions on Base Types
Programming Language Functions on Composite Types
Caveats

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

Overview
Description
Examples

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 — Connects your procedure to the SPI manager.
SPI_finish — Disconnects your procedure from the SPI manager.
SPI_exec — Creates an execution plan (parser+planner+optimizer) and executes a query.
SPI_prepare — Connects your procedure to the SPI manager.
SPI_saveplan — Saves a passed plan
SPI_execp — Executes a plan from SPI_saveplan

Interface Support Functions

SPI_copytuple — Makes copy of tuple in upper Executor context
SPI_modifytuple — Modifies tuple of relation
SPI_fnumber — Finds the attribute number for specified attribute
SPI_fname — Finds the attribute name for the specified attribute
SPI_getvalue — Returns the string value of the specified attribute
SPI_getbinval — Returns the binary value of the specified attribute
SPI_gettype — Returns the type name of the specified attribute
SPI_gettypeid — Returns the type OID of the specified attribute
SPI_getrelname — Returns the name of the specified relation
SPI_palloc — Allocates memory in upper Executor context
SPI_repalloc — Re-allocates memory in upper Executor context
SPI_pfree — Frees memory from upper Executor context

Memory Management

Visibility of Data Changes

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

Database Connection Functions

Query Execution Functions

Asynchronous Query Processing

Asynchronous Notification

Functions Associated with the COPY Command

libpq Tracing Functions

libpq Control Functions

User Authentication Functions

Environment Variables

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

pgtcl Command Reference Information

pg_connect — opens a connection to the backend server
pg_disconnect — closes a connection to the backend server
pg_conndefaults — obtain information about default connection parameters
pg_exec — send a query string to the backend
pg_result — get information about a query result
pg_select — loop over the result of a SELECT statement
pg_listen — sets or changes a callback for asynchronous NOTIFY messages
pg_lo_creat — create a large object
pg_lo_open — open a large object
pg_lo_close — close a large object
pg_lo_read — read a large object
pg_lo_write — write a large object
pg_lo_lseek — seek to a position in a large object
pg_lo_tell — return the current seek position of a large object
pg_lo_unlink — delete a large object
pg_lo_import — import a large object from a Unix file
pg_lo_export — export a large object to a Unix file

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

Windows Applications

Writing Applications

Unix Installation

Building the Driver

Configuration Files

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. 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

23. pg_options

24. 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

25. Frontend/Backend Protocol

Startup
Query
Function Call
Notification Responses
Cancelling Requests in Progress
Termination

Message Data Types

Message Formats

26. Postgres Signals

27. gcc Default Optimizations

28. Backend Interface

BKI File Format
General Commands
Macro Commands
Debugging Commands
Example

29. 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

Hardcopy Generation for v6.5

RTF Cleanup Procedure

RPM installation on Linux
Manual installation of tools

Alternate Toolsets

Bibliography

		Next
		Summary