Nytro Posted May 4, 2011 Report Posted May 4, 2011 Practical PostgreSQLJohn WorsleyCommand Prompt, Inc.Joshua DrakeCommand Prompt, Inc.Edited byAndrew BrookinsMichael HollowayCopyright © 2001-2002 by Commandprompt, IncCopyright © 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 ContentsPreface 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 QuestionsI. 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 PostgreSQLII. 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 PostgreSQLIII. 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 PrivilegesIV. 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 VariablesOnline:http://www.commandprompt.com/ppbook/ Quote
