Practical PostgreSQL

John Worsley

Command Prompt, Inc.

Joshua Drake

Command Prompt, Inc.

Edited by

Andrew Brookins

Michael Holloway

Copyright (c) 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 in the CD?
Conventions Used in This Book
Acknowledgments
Comments and Questions
I. Introduction and Installation
1. What is PostgreSQL?
Open Source Free Version
Commercial PostgreSQL Products
Open Source Versus Commercial Products
The Bottom Line
Commercial Support
Community Support
PostgreSQL Feature Set
Where to Proceed from Here
2. Installing PostgreSQL
Preparing for Installation
Required Software Packages
Optional Packages
Disk Space
10 Steps to Installing PostgreSQL
Step 1: Creating the "postgres" User
Step 2: Installing the PostgreSQL Source Package
Step 3: Configuring the Source Tree
Step 4: Compiling the Source
Step 5: Regression Testing
Step 6: Installing Compiled Programs and Libraries
Step 7: Setting Environment Variables
Step 8: Initializing and Starting PostgreSQL
Step 9: Configuring the PostgreSQL SysV Script
Step 10: Creating a Database
II. Using PostgreSQL
3. Understanding SQL
Introduction to SQL
A Brief History of SQL
SQL and its Predecessors
SQL Standards
Introduction to Relational Databases
Understanding Databases
Understanding Tables
SQL Statements
The Anatomy of a SQL Statement
Token Formatting Considerations
Keywords and Identifiers
Constants
Special Character Symbols
Comments
Putting it All Together
Data Types
NULL Values
Boolean Values
Character Types
Numeric Types
Date and Time Types
Geometric Types
Arrays
Type Coercion
Tables in PostgreSQL
System Columns
Object Identifiers
Planning Ahead
4. Using SQL with PostgreSQL
Introduction to psql
Starting psql
Introduction to psql Syntax
Executing Queries
Using Tables
Creating Tables with CREATE TABLE
Altering Tables with ALTER TABLE
Re-structuring Existing Tables
Destroying Tables with DROP TABLE
Adding Data with INSERT and COPY
Inserting new values
Inserting values from other tables with SELECT
Copying values from external files with COPY
Retrieving Rows with SELECT
A Simple SELECT
Specifying Target Columns
Expressions, Constants and Aliases
Selecting Sources with the FROM Clause
Aliasing FROM Sources
Removing Duplicate Rows with DISTINCT
Qualifying with the WHERE Clause
Joining Data Sets with JOIN
Grouping Rows with GROUP BY
Sorting Rows with ORDER BY
Setting Rw Range with LIMIT and OFFSET
Comparing Data Sets with UNION, INTERSECT and EXCEPT
Using Case Expressions
Creating Tables from Other Tables
Modifying Rows with UPDATE
Updating Entire Columns
Updating Several Columns
Updating from Several Sources
Removing Rows with DELETE
Using Sub-Queries
Using Views
Creating a View
Applying Views
Destroying a View
Further SQL Application
5. Operators and Functions
Operators
Using Operators
Character String Operators
Numeric Operators
Logical Operators
Using Operators with NULL
Operator Precedence
Functions
Mathematical Functions
Character String Functions
Date and Time Functions
Type Conversion Functions
Aggregate Functions
6. PostgreSQL Clients
The psql Client: Advanced Topics
Command Explanations
Entering Queries using External Files
The Readline and History Libraries
Variable Substitution
About the Prompt
Modifying the Prompt
Prompt Examples
PgAccess: A Graphical Client
Installation and Basic Configuration
Managing Users
Managing Groups
Creating Databases
Creating Tables
Using Queries
Creating Functions
7. Advanced Features
Indices
Creating an Index
Destroying an Index
Advanced Table Techniques
Using Constraints
Inheritance
Arrays
Creating an Array Column
Inserting Values into Array Columns
Selecting Values from Array Columns
Updating Values in Array Columns
Automating Common Routines
Sequences
Triggers
Transactions and Cursors
Using Transaction Blocks
Using Cursors
Extending PostgreSQL
Creating New Functions
Creating New Operators
III. Administrating PostgreSQL
8. Authentication and Encryption
Client Authentication
Password Authentication
The "pg_hba.conf" File
Authentication Failure
Encrypting sessions
Built-in SSL
SSH/OpenSSH
Configuring and Using Stunnel
9. Database Management
Starting and Stopping PostgreSQL
Using pg_ctl
Using the SysV Script
Creating and Removing a Database
Creating a Database
Removing a Database
Maintaining a Database
Using VACUUM
Using vacuumdb
Documenting a Database
Backing up and Restoring Data
Using pg_dump
Using pg_dumpall
Restoring a Database
When to Backup and Restore Data
Backing Up the File System
10. User and Group Management
Managing Users
Viewing Users
Creating Users
Altering Users
Removing Users
Managing Groups
Creating and Removing Groups
Associating Users with Groups
Granting Privileges
Understanding Access Control
Granting privileges with GRANT
Restricting rights with REVOKE
Using Views for Access Control
IV. Programming with PostgreSQL
11. PL/pgSQL
Introduction to PL/pgSQL
Adding PL/pgSQL to your Database
Step 1: Add PL/pgSQL to your Database
Step 2: Start Programming in PL/pgSQL
Language Structure
Code Blocks
Comments
Statements and Expressions
Using Variables
Data types
Assignment
Argument Variables
Returning Variables
Attributes
Declaration
Concatenation
Renaming
Controlling Program Flow
Conditional statements
Loops
Handling Errors and Exceptions
Calling Functions
PL/pgSQL and Triggers
12. JDBC
Building the PostgreSQL JDBC Driver
Using the PostgreSQL Driver
Basic JDBC Syntax
Basic JDBC Usage
Using Advanced JDBC Features
Issues Specific to PostgreSQL and JDBC
V. Appendices
A. Multi-Byte Encoding Types
VI. PostgreSQL Reference
13. PostgreSQL Reference Guide
ABORT — Rolls back changes made during a transaction block.
ALTER GROUP — Modifies the structure of a user group.
ALTER TABLE — Modifies table, row, and column attributes.
ALTER USER — Modifies user account 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 — Copeis filesystem data and row data between files andt tables. Copies data between files and tables
CREATE AGGREGATE — Defines a new aggregate function within the database.
CREATE CONSTRAINT TRIGGER — Creates a trigger for use with a constraint.
CREATE DATABASE — Creates a new database on the system.
CREATE FUNCTION — Defines a new function within the database.
CREATE GROUP — Creates a new user group within the database.
CREATE INDEX — Constructs 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 user account.
CREATE VIEW — Creates a new view on a table.
createdb — Creates a new database from the command line.
createlang — Defines a new programming language for use by functions within a database.
createuser — Adds a new user account to a database.
SQL_CURRENT_DATE — Returns the current date.
SQL_CURRENT_TIME — Returns the current time.
SQL_CURRENT_TIMESTAMP — Returns the current date and time.
SQL_CURRENT_USER — Returns the current database username.
DECLARE — Defines a new cursor.
DELETE — Removes rows from a table.
DROP AGGREGATE — Removes the definition of an aggregate function from the database.
DROP DATABASE — Removes a database from the system.
DROP FUNCTION — Removes a user-defined C function.
DROP GROUP — Removes a user group from the database.
DROP INDEX — Removes an index from a database.
DROP LANGUAGE — Removes the definition of a procedural language.
DROP OPERATOR — Removes an operator from the database.
DROP RULE — Removes a rule from a database.
DROP SEQUENCE — Removes an existing sequence from a database.
DROP TABLE — Removes a table from a database
DROP TRIGGER — Removes the definition of a trigger from a database.
DROP TYPE — Removes a type from the system catalogs.
DROP USER — Removes a user account from a database.
DROP VIEW — Removes an existing view from a database.
dropdb — Removes a database from the system.
droplang — Removes the definition of a procedural language from a database.
dropuser — Removes a user account from a database.
ecpg — The embedded SQL C preprocessor.
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, group, or all of the users in the database.
initdb — Creates a new database cluster.
initlocation — Create a secondary PostgreSQL database storage area
INSERT — Inserts new rows into a table.
ipcclean — Cleans shared memory resources left behind by aborted backend processes.
LISTEN — Listen for a notification event.
LOAD — Dynamically loads object files into a database.
LOCK — Explicitly locks a specified table within the current transaction.
MOVE — Repositions the cursor to another row.
NOTIFY — Signals all backends that are listening for the specified notify event.
pg_dump — Exports a database to a script file.
pg_dumpall — Exports all databases on the system to a script file.
pg_ctl — Starts, stops, and restarts postmaster.
pgtclsh — The TCL database client.
pgtksh — A graphical TCL/TK database client.
postgres — Runs a single-user backend process.
postmaster — Executes the PostgreSQL multi-user backend process.
psql — The PostgreSQL interactive terminal.
REINDEX — Rebuilds indexes on tables.
RESET — Restores run-time variables to their default settings.
REVOKE — Revokes access privileges from a user, a group, or all users.
ROLLBACK — Aborts the current transaction black 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 run-time variables.
SET CONSTRAINTS — Sets the constraint mode for the current transaction block.
SET TRANSACTION — Sets the transaction isolation mode for the current transaction block.
SHOW — Displays the values of run-time 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.
vacuumdb — Cleans and analyzes a database.
List of Tables
2-1. Configuration options
3-1. An Example SQL Table
3-2. Fundamental PostgreSQL Commands
3-3. PostgreSQL Supported C-Style Escape Sequences
3-4. Floating Point Representations
3-5. Punctuation Symbols
3-6. Fundamental PostgreSQL Operators
3-7. A Simple SQL Query
3-8. UPDATE Example: The SET Clause
3-9. UPDATE Example: The WHERE Clause
3-10. PostgreSQL Supported Data Types
3-11. Supported True or False Constants
3-12. Character Types
3-13. Numeric Types Overview
3-14. Date and Time Types
3-15. Valid Date Formats
3-16. Month Abbreviations
3-17. Day of the Week Abbreviations
3-18. Date Output Formats
3-19. Extended Date Output Formats
3-20. Valid Time Formats
3-21. Valid Time Zone Formats
3-22. Some Valid Timestamp Formats
3-23. Date and Time Constants
3-24. Geometric Types
3-25. System Columns
3-26. The Authors Table
3-27. The Subjects Table
4-1. UPDATE Syntax
4-2. DELETE Syntax
4-3. CREATE VIEW Syntax
4-4. The "shipments" Table
5-1. Basic Character String Operators
5-2. Regular Expression Comparison Operators
5-3. Regular Expression Symbols
5-4. Mathematical Operators
5-5. Comparison Operators
5-6. Bit String Operators
5-7. The AND, OR and NOT Operators
5-8. Operator Precedence
5-9. Mathematical Functions in PostgreSQL
5-10. Character String Functions
5-11. Date and Time Functions
5-12. Timestamp and Interval Units
5-13. Type Conversion Functions
5-14. Numeric Conversion Formatting Characters
5-15. Timestamp Conversion Formatting Characters
5-16. Aggregate Functions
6-1. Default PROMPT Settings
6-2. Prompt Substitution Strings
7-1. Sequence Attributes
7-2. The "shipments" Table
7-3. The "pg_trigger" Table
8-1. The "pg_shadow" Table
10-1. The "pg_shadow" Table
10-2. PostgreSQL ACL Privileges
10-3. The "stock" Table
11-1. Possible Level Values
11-2. Trigger Function Variables
A-1. Multi-Byte Encoding Types
13-1. Operators vs. index
13-1. Share modes
13-1. The following slash-commands are defined:
13-2. \pset Options
13-1. Join type options
13-2. SELECT Clauses
List of Figures
6-1. PgAccess
6-2. The PgAccess User Tab
6-3. The PgAccess Create Table Dialog
6-4. The PgAccess Visual Designer
6-5. The Create Function Dialog
List of Examples
2-1. Verifying gmake
2-2. Verifying GCC
2-3. Verifying gzip and tar
2-4. Verifying Disk Space
2-5. Adding the "postgres" User
2-6. Unpacking the PostgreSQL Source Package
2-7. Compiling the Source with GNU make
2-8. Making Regression Tests
2-9. Regression Check Output
2-10. The "gmake install" Command
2-11. Installing Perl and Python modules manually
2-12. Installing All Headers
2-13. Setting LD_LIBRARY_PATH in a Bash Shell
2-14. Setting LD_LIBRARY_PATH in csh and tcsh
2-15. Initializing the Database
2-16. Output from "initdb"
2-17. Running "postmaster" in the Foreground
2-18. Running "postmaster" in the background
2-19. Copying the Linux Script
2-20. Making the Linux Script Executable
2-21. Starting PostgreSQL with the SysV Script
2-22. Creating a Database
2-23. Accessing a Database with "psql"
2-24. Querying a System Table
3-1. Spaces and Newlines
3-2. Keywords and Commands
3-3. Bending Rules
3-4. Using String Constants
3-5. Multi-line String Constants
3-6. Using Bit String Constants
3-7. Using Integer Constants
3-8. Valid Floating Point Values
3-9. The Difference Between true and 'true'
3-10. Operators in Statements
3-11. Single-line comments
3-12. Multi-line comments
3-13. Example SQL Query
3-14. A SQL Update
3-15. Observing NULL Values
3-16. Using NULL Values
3-17. Simple Boolean Table
3-18. Checking Boolean Values
3-19. Implying Boolean 'true'
3-20. Checking for 'false' Boolean Values
3-21. Correcting Null Values
3-22. Avoiding Overflow Errors
3-23. A numeric Alternative to money
3-24. Using the serial Data Type
3-25. Accomplishing the Same Goal Manually
3-26. Setting Date Formats
3-27. Interpreting Interval Formats
3-28. Using the current and now Constants
3-29. Comparing now to current
3-30. Using Type Conversion Functions
3-31. Differentiating Rows via the OID
4-1. Setting System Path for psql
4-2. Listing psql Slash Commands
4-3. Entering Statements into psql
4-4. Leaving end-characters open
4-5. Setting the EDITOR Variable
4-6. Creating the "books" Table
4-7. The \d Command's Output
4-8. Syntax of the ALTER TABLE Command
4-9. Adding a Column
4-10. Altering Column Defaults
4-11. Re-naming a Table
4-12. Re-naming a Column
4-13. Adding a Foreign Key to a Table
4-14. Changing Table Ownership
4-15. Re-structuring a Table with CREATE TABLE AS
4-16. Re-structuring a Table with CREATE TABLE and INSERT INTO
4-17. Inserting New Values into the "books" Table
4-18. Changing the Order of Target Columns
4-19. Inserting Values from Another Table
4-20. An Example ASCII Copy File
4-21. Copying an ASCII File
4-22. Copying a Binary File
4-23. Copying the books Table to an ASCII File
4-24. Selecting All from the "books" Table
4-25. Re-Ordering Columns
4-26. Using Expressions and Constants
4-27. Using the AS Clause with Expressions and Constants
4-28. Selecting from Multiple Table Sources
4-29. Selecting from a Sub-query
4-30. Aliasing FROM Sources
4-31. Aliasing Columns
4-32. Using DISTINCT
4-33. A Simple WHERE Clause
4-34. Combining Conditions in the WHERE Clause
4-35. Grouping WHERE Conditions with Parentheses
4-36. A Simple CROSS JOIN
4-37. Comparing INNER JOIN to WHERE
4-38. The NATURAL and USING Clauses
4-39. Inner Joins versus Outer Joins
4-40. Joining Many Data Sources
4-41. Using GROUP BY
4-42. Using the HAVING Clause
4-43. Using ORDER BY
4-44. Using ORDER BY with Multiple Expressions
4-45. Using DISTINCT with ORDER BY
4-46. Using LIMIT and OFFSET
4-47. Using UNION
4-48. Using INTERSECT
4-49. Using EXCEPT
4-50. Comparing Sub-Query Result Sets
4-51. Using Case Expressions in Statements
4-52. Using Case Expressions with Sub-Queries
4-53. Using SELECT INTO
4-54. A Simple UPDATE
4-55. Updating Entire Columns
4-56. Using UPDATE on Several Columns
4-57. Using UPDATE with Several Sources
4-58. Deleting Rows from a Table
4-59. Deleting All Table Rows
4-60. A Simple Sub-Query
4-61. A Sub-query Using IN
4-62. A Multi-Column Sub-query Using IN
4-63. Creating a View
4-64. Using a View
5-1. Correct Operator Usage
5-2. Incorrect Operator Usage
5-3. Comparing Strings
5-4. Concatenating Strings
5-5. An Example Regular Expression
5-6. A Simple Regular Expression Comparison
5-7. A More Involved Regular Expression Comparison
5-8. A Complicated Regular Expression Comparison
5-9. Using Mathematical Operators
5-10. Using Comparison Operators
5-11. Using BETWEEN
5-12. Operator Equivalents to BETWEEN
5-13. Shifting Bit Strings
5-14. Combining Comparisons with Boolean Operators
5-15. Comparisons Using IS NULL
5-16. Comparisons Equal to NULL
5-17. Using Operators with NULL Values
5-18. Operator Precedence
5-19. Using Aggregate Expressions
6-1. Inserting a File into the Current Buffer
6-2. Setting a Variable
6-3. The Variable List
6-4. Using Interpolation During an INSERT
6-5. Using Interpolation During a SELECT
6-6. Reading from a File into a Variable
6-7. Using a Variable in an INSERT
6-8. Setting the Prompt Variables
6-9. Customizing the Prompt with Database Host, Username, Port, and Username
6-10. Customizing the Prompt with the Date, Database Name, and Username
7-1. Creating an Index
7-2. Implicit Index Creation
7-3. Creating a Unique Index
7-4. Specifying an Index Type
7-5. Creating a Functional Index
7-6. Dropping an Index
7-7. Creating a Table with Column Constraints
7-8. Creating a Table with Table Constraints
7-9. Adding a Constraint to an Existing Table
7-10. "Removing" a Constraint
7-11. Creating a Child Table
7-12. Inserting into a Child Table
7-13. Selecting with Inheritance
7-14. Modifying Parent and Child Tables
7-15. Modifying Parent Tables with ONLY
7-16. Creating a Table with an Array Column
7-17. Creating a Table with a Multi-Dimensional Array Column
7-18. Inserting Array Constants
7-19. Inserting Values into Multi-Dimensional Arrays
7-20. Selecting Entire Array Values
7-21. Selecting Array Values with Subscripts
7-22. Avoiding NULL Values in Arrays
7-23. Selecting From a Multi-Dimensional Array
7-24. Selecting Array Values with Slices
7-25. Using array_dims()
7-26. Completely Modifying an Array
7-27. Modifying an Array Subscript
7-28. Creating a Sequence
7-29. Viewing a Sequence
7-30. Incrementing a Sequence
7-31. Using currval()
7-32. Setting a Sequence Value
7-33. Removing a Sequence
7-34. Checking Sequence Dependencies
7-35. Creating the check_shipment Trigger
7-36. Dropping a Trigger
7-37. Selecting a Trigger's Assigned Table
7-38. Beginning a Transaction
7-39. Committing a Transaction
7-40. Rolling Back a Transaction
7-41. Recovering from the Abort State
7-42. Declaring a Cursor
7-43. Fetching Rows from a Cursor
7-44. Moving a Cursor
7-45. Closing a Cursor
7-46. Creating a SQL Function
7-47. Using a SQL Function
7-48. is_zero.c - A Simple C Function
7-49. Creating a C Function
7-50. Overloading a C Function
7-51. Using a C Function
7-52. Dropping a Function
7-53. Creating a User-Defined Operator
7-54. Using a User-Defined Operator
7-55. Overloading a User-Defined Operator
7-56. Using an Overloaded Operator
7-57. Dropping an Operator
7-58. Dropping an Overloaded Operator
8-1. A Simple "pg_hba.conf" File
8-2. Checking User Permissions
8-3. A Valid pg_hba.conf Entry with Spaces and Tabs
8-4. Valid pg_hba.conf Comments
8-5. Host Entry Syntax
8-6. Single Host Entry
8-7. Rejection Entry
8-8. Single Host, Single Database Entry
8-9. Small Network Connection Entry
8-10. Larger Network Connection Entry
8-11. An "ident" Configuration in "pg_hba.conf"
8-12. A "pg_ident.conf" Configuration
8-13. A "sameuser" Configuration
8-14. Making an SSH Tunnel to PostgreSQL
8-15. Using Stunnel Remotely
8-16. Using Stunnel Locally
8-17. An Example inetd Entry
8-18. An Example xinetd Entry
9-1. Starting PostgreSQL with pg_ctl
9-2. Stopping PostgreSQL with pg_ctl
9-3. Re-starting PostgreSQL with pg_ctl
9-4. Checking Status with pg_ctl
9-5. Starting PostgreSQL with Service Command
9-6. Checking Status with postgresql Script
9-7. Checking "usecreatedb" Rights
9-8. Creating a Database
9-9. Using the createdb Application
9-10. Using DROP DATABASE
9-11. Using the dropdb Command
9-12. Using VACUUM on a Table
9-13. Using VACUUM ANALYZE on a Database
9-14. Using vacuumdb on All Databases
9-15. Using vacuumdb on a Remote Database
9-16. Commenting the "books" Table
9-17. Retrieving a Comment
9-18. Using pg_dump
9-19. Using pg_dump Remotely
9-20. Using pg_dumpall
9-21. Re-Creating the "booktown" Database
9-22. Restore with pg_restore
9-23. Backing Up the PostgreSQL Filesystem
10-1. Creating a Normal User
10-2. Creating a User with CREATEDB Rights
10-3. Creating a Superuser
10-4. Creating a User with createuser
10-5. Interactively Creating a User with createuser
10-6. Resetting a Password
10-7. Adding Superuser Rights
10-8. Removing Superuser Rights
10-9. Removing a User with DROP USER
10-10. Removing a User with dropuser
10-11. Creating a Group
10-12. Verifying a Group
10-13. Removing a Group
10-14. Adding a User to a Group
10-15. Verifying User Addition
10-16. Removing a User from a Group
10-17. Granting User Privileges
10-18. Granting Group Privileges
10-19. Revoking Rights
10-20. Controlling SELECT Privileges with a View
10-21. Controlling SELECT
11-1. Creating the PL/pgSQL Call Handler
11-2. Adding PL/pgSQL with CREATE LANGUAGE
11-3. Using createlang as a Database Superuser
11-4. Explicitly Passing a Superuser Account Name to createlang
11-5. Structure of a PL/pgSQL Code Block.
11-6. Using Single Line Comments
11-7. Using Block Comments
11-8. Using Expressions
11-9. Output of a_function()
11-10. Using Timestamp Values Correctly
11-11. Default Value Assignment
11-12. Syntax of a SELECT INTO Statement
11-13. Using the SELECT INTO Statement
11-14. Result of the get_customer_id() Function
11-15. Using the FOUND Boolean in get_customer_id()
11-16. Result of the New get_customer_id() Function
11-17. Function Call Examples
11-18. Directly Using Argument Variables
11-19. Syntax of the ALIAS Keyword
11-20. Using PL/pgSQL Aliases
11-21. Result of the triple_price() Function
11-22. Syntax of the RETURN Statement
11-23. Declaring a Variable with %TYPE
11-24. Using the %TYPE Attribute
11-25. Results of the get_author() Function
11-26. Using the %ROWTYPE Attribute
11-27. Results of the New get_author() Function
11-28. Declaring a PL/pgSQL Variable
11-29. Variable Declarations
11-30. Variable Declaration Options
11-31. Using Variable Declaration Options
11-32. Returning a Concatenated String
11-33. Assigning a Concatenated Value to a String
11-34. RENAME Syntax
11-35. Using the RENAME Keyword
11-36. Syntax of an IF/THEN Statement
11-37. Using the IF/THEN Statement
11-38. Results of the stock_amount() Function
11-39. Syntax of an IF/THEN/ELSE Statement
11-40. Using the IF/THEN/ELSE Statement
11-41. Results of the in_stock() Function
11-42. Syntax of an IF/THEN/ELSE/IF Statement
11-43. Using the IF/THEN/ELSE/IF Statement
11-44. Results of the books_by_subject() Function
11-45. Unconditional Loop Syntax
11-46. EXIT Statement Syntax
11-47. Using the Basic Loop
11-48. Result of the square_integer_loop() Function
11-49. WHILE Loop Syntax
11-50. Using the WHILE Loop
11-51. Syntax of the FOR Loop
11-52. Using the FOR Loop
11-53. Syntax of a FOR Loop Used With RECORD Variables
11-54. Using the FOR Loop with %ROWTYPE
11-55. Result of the extract_title() Function
11-56. Result of the extract_title Function Using RECORD
11-57. Syntax of the RAISE Statement
11-58. Using the RAISE Statement
11-59. Results of the raise_test() Function
11-60. Syntax of the PERFORM Keyword
11-61. Using the PERFORM Keyword
11-62. Creating Trigger Functions
11-63. The check_shipment_addition() PL/pgSQL Trigger Function
11-64. The check_shipment Trigger
12-1. Class Name Lookup
12-2. A Simple JDBC Connection
12-3. A JDBC Statement Object
12-4. A Simple JDBC Select
12-5. A Simple JDBC Insert
12-6. A JDBC Prepared Statement
12-7. JDBC ResultSetMetaData
12-8. JDBC Database MetaData
12-9. JDBC First Row Fetch
13-1. Accessing Database Permissions