Practical PostgreSQL

[Nytro]

Practical PostgreSQL

John Worsley

Command Prompt, Inc.

Joshua Drake

Command Prompt, Inc.

Edited by

Andrew Brookins

Michael Holloway

Copyright © 2001-2002 by Commandprompt, Inc

Copyright © 2001 by Command Prompt, Inc. This material may be distributed only subject to the terms and conditions set forth in the Open Publication License, v1.0 or later (the latest version is presently available at http://www.opencontent.org/openpub/).

'Distribution of substantively modified versions of this document is prohibited without the explicit permission of the copyright holder.' to the license reference or copy.

'Distribution of the work or derivative of the work in any standard (paper) book form is prohibited unless prior permission is obtained from the copyright holder.' to the license reference or copy.

Although every reasonable effort has been made to incorporate accurate and useful information into this book, the copyright holders make no representation about the suitability of this book or the information therein for any purpose. It is provided "as is" without expressed or implied warranty.

Table of Contents
Preface

    Who Is the Intended Audience?
    Structure of This Book
    Platform and Version Used
    What Is Included on the CD?
    Conventions Used in This Book
    Acknowledgments
    Comments and Questions

I. Introduction and Installation

    1. What is PostgreSQL?

        Open Source Free Version
        PostgreSQL Feature Set
        Where to Proceed from Here

    2. Installing PostgreSQL

        Preparing for Installation
        10 Steps to Installing PostgreSQL

II. Using PostgreSQL

    3. Understanding SQL

        Introduction to SQL
        Introduction to Relational Databases
        SQL Statements
        Data Types
        Tables in PostgreSQL

    4. Using SQL with PostgreSQL

        Introduction to psql
        Using Tables
        Adding Data with INSERT and COPY
        Retrieving Rows with SELECT
        Modifying Rows with UPDATE
        Removing Rows with DELETE
        Using Sub-Queries
        Using Views
        Further SQL Application

    5. Operators and Functions

        Operators
        Functions

    6. PostgreSQL Clients

        The psql Client: Advanced Topics
        PgAccess: A Graphical Client

    7. Advanced Features

        Indices
        Advanced Table Techniques
        Arrays
        Automating Common Routines
        Transactions and Cursors
        Extending PostgreSQL

III. Administrating PostgreSQL

    8. Authentication and Encryption

        Client Authentication
        Encrypting sessions

    9. Database Management

        Starting and Stopping PostgreSQL
        Initializing the Filesystem
        Creating and Removing a Database
        Maintaining a Database
        Backing Up and Restoring Data

    10. User and Group Management

        Managing Users
        Managing Groups
        Granting Privileges

IV. Programming with PostgreSQL

    11. PL/pgSQL

        Adding PL/pgSQL to your Database
        Language Structure
        Using Variables
        Controlling Program Flow
        PL/pgSQL and Triggers

    12. JDBC

        Building the PostgreSQL JDBC Driver
        Using the PostgreSQL Driver
        Using JDBC
        Issues Specific to PostgreSQL and JDBC

    13. LXP

        Why Use LXP?
        Core Features
        Installing and Configuring LXP
        Understanding LXP Mark-Up
        LXP Variables and Objects
        Using Cookies with LXP
        Tag Parsing
        Branching Logic
        Loop Iteration
        Content Inclusion
        Displaying Foreign Tags with <xtag>

V. PostgreSQL Command Reference

    14. PostgreSQL Command Reference

        ABORT  -- Rolls back changes made during a transaction block.
        ALTER GROUP  -- Modifies the structure of a user group.
        ALTER TABLE  -- Modifies table and column attributes.
        ALTER USER  -- Modifies user properties and permissions.
        BEGIN  -- Starts a chained-mode transaction block.
        CLOSE  -- Closes a previously defined cursor object.
        CLUSTER  -- Provides the backend server with clustering information about a table.
        COMMENT  -- Adds a comment to an object within the database.
        COMMIT  -- Ends the current transaction block and finalizes changes made within it.
        COPY  -- Copies data between files and tables.
        CREATE AGGREGATE  -- Defines a new aggregate function within the database.
        CREATE DATABASE  -- Creates a new database in PostgreSQL.
        CREATE FUNCTION  -- Defines a new function within the database.
        CREATE GROUP  -- Creates a new PostgreSQL group within the database.
        CREATE INDEX  -- Places an index on a table.
        CREATE LANGUAGE  -- Defines a new language to be used by functions.
        CREATE OPERATOR  -- Defines a new operator within the database.
        CREATE RULE  -- Defines a new rule on a table.
        CREATE SEQUENCE  -- Creates a new sequence number generator.
        CREATE TABLE  -- Creates a new table.
        CREATE TABLE AS  -- Creates a new table built from data retrieved by a SELECT.
        CREATE TRIGGER  -- Creates a new trigger.
        CREATE TYPE  -- Defines a new data type for use in the database.
        CREATE USER  -- Creates a new PostgreSQL database user.
        CREATE VIEW  -- Creates a view on a table.
        CURRENT_DATE  -- Returns the current date.
        CURRENT_TIME  -- Returns the current time.
        CURRENT_TIMESTAMP  -- Returns the current date and time.
        CURRENT_USER  -- Returns the current database username.
        DECLARE  -- Defines a new cursor.
        DELETE  -- Removes rows from a table.
        DROP AGGREGATE  -- Removes an aggregate function from a database.
        DROP DATABASE  -- Removes a database from the system.
        DROP FUNCTION  -- Removes a user-defined function.
        DROP GROUP  -- Removes a user group from the database.
        DROP INDEX  -- Removes an index from a database.
        DROP LANGUAGE  -- Removes a procedural language from a database.
        DROP OPERATOR  -- Removes an operator from the database.
        DROP RULE  -- Removes a rule from a database.
        DROP SEQUENCE  -- Removes a sequence from a database.
        DROP TABLE  -- Removes a table from a database.
        DROP TRIGGER  -- Removes a trigger definition from a database.
        DROP TYPE  -- Removes a type from the system catalogs.
        DROP USER  -- Removes a PostgreSQL user.
        DROP VIEW  -- Removes an existing view from a database.
        END  -- Ends the current transaction block and finalizes its modifications.
        EXPLAIN  -- Shows the statement execution plan for a supplied query.
        FETCH  -- Retrieves rows from a cursor.
        GRANT  -- Grants access privileges to a user, a group, or to all users in the database.
        INSERT  -- Inserts new rows into a table.
        LISTEN  -- Listen for a notification event.
        LOAD  -- Dynamically loads object files into a database.
        LOCK  -- Locks a table within a transaction.
        MOVE  -- Repositions a cursor to another row.
        NOTIFY  -- Signals all backends that are listening for the specified notify event.
        REINDEX  -- Rebuilds indices on tables.
        RESET  -- Restores runtime variables to their default settings.
        REVOKE  -- Revokes access privileges from a user, a group, or all users.
        ROLLBACK  -- Aborts the current transaction block and abandons any modifications it would have made.
        SELECT  -- Retrieves rows from a table or view.
        SELECT INTO  -- Construct a new table from the results of a SELECT.
        SET  -- Set runtime variables.
        SET CONSTRAINTS  -- Sets the constraint mode for the current transaction block.
        SET TRANSACTION  -- Sets the transaction isolation level for the current transaction block.
        SHOW  -- Displays the values of runtime variables.
        TRUNCATE  -- Empties the contents of a table.
        UNLISTEN  -- Stops the backend process from listening for a notification event.
        UPDATE  -- Modifies the values of column data within a table.
        VACUUM  -- Cleans and analyzes a database.

VI. Appendixes

    A. Multibyte Encoding Types
    B. Backend Options for postgres
    C. Binary COPY Format

        The Header
        Tuples
        Trailer

    D. Internal psql Variables

Online:

http://www.commandprompt.com/ppbook/