PostgreSQL Developer's Guide by Ahmed, Ibrar -- Read -- Imperial Library of Trantor

Index

PostgreSQL Developer's Guide

Table of Contents PostgreSQL Developer's Guide Credits About the Authors About the Reviewers www.PacktPub.com

Support files, eBooks, discount offers, and more

Why subscribe? Free access for Packt account holders

Preface

What this book covers What you need for this book Who this book is for Conventions Reader feedback Customer support

Downloading the example code Errata Piracy Questions

1. Getting Started with PostgreSQL

Writing queries using psql Creating, altering, and truncating a table

Creating tables Altering tables Truncating tables

Inserting, updating, and deleting data from tables

Inserting data Updating data Deleting data

PostgreSQL's supported data types PostgreSQL's operators and usage

Logical operators Comparison operators Mathematical operators

Constraints in PostgreSQL

Unique constraints Not-null constraints Exclusion constraints Primary key constraints Foreign key constraints Check constraints

Privileges in PostgreSQL Summary

2. The Procedural Language

Why should you use PL/pgSQL? Installing PL/pgSQL Understanding the structure of PL/pgSQL Using comments in PL/pgSQL Declaring variables in PL/pgSQL Declaring function parameters Declaring the %TYPE attribute Declaring the row-type and record type variables Statements and expressions

Using statements The assignment statement The call/return function The RETURN expression Exception handling statements Compound statements Expressions

Control structures

Conditional statements

IF-THEN IF-THEN-ELSE IF-THEN-ELSIF Simple CASE Searched CASE

Loops

The simple loop The WHILE loop The FOR loop

Exception handling Native support for other procedural languages

PL/Python

Installing PL/Python Writing functions in PL/Python Handling arguments with PL/Python Accessing the database with PL/Python

PL/Perl

Installing PL/Perl Writing functions in PL/Perl Handling arguments with PL/Perl Accessing the database with PL/Perl

PL/Tcl

Installing PL/Tcl Writing functions in PL/Tcl Handling arguments with PL/Tcl Accessing the database with PL/Tcl

Summary

3. Working with Indexes

What is an index? How to create an index How to drop an index Types of indexes

The single-column index The multicolumn index The partial index The unique index Explicitly creating an index using the CREATE INDEX command The expression index The implicit index The concurrent index

Index types

The B-tree index The hash index The GiST index The GIN index

Index bloating

Dump and restore VACUUM CLUSTER

Reindexing Points to ponder Summary

4. Triggers, Rules, and Views

Understanding triggers

Tricky triggers Creating triggers and trigger functions with PL/pgSQL

Creating triggers on views Creating triggers in PL/Perl Creating triggers in PL/Tcl Creating triggers in PL/Python

PostgreSQL rules

Rules versus triggers – creating updateable views with rules

Understanding views

Materialized views

Creating materialized views

Summary

5. Window Functions

Understanding window functions

The cume_dist() function The row_number() function The rank() function The dense_rank() function The percent_rank() function The first_value() function The last_value() function The nth_value() function The ntile() function The lag() function The lead() function

Summary

6. Partitioning

Creating a table partition Partitioning in PostgreSQL

Range partition

Creating the master table Creating a range partition table Creating an index on child tables Creating a trigger on the master table Enabling the constraint exclusion

Performing DML operations on a partition table Handling the UPDATE and DELETE statements on a partition table List partition

Summary

7. Query Optimization

What is EXPLAIN?

Working with EXPLAIN ANALYZE EXPLAIN VERBOSE EXPLAIN pretty formats

Cost parameters Sequential scans Index scans

Index-only scans

Bitmap scans Common Table Expressions Joins

Nested loop joins Merge joins Hash joins Hash semi and anti joins Join ordering

Query planning

Window functions

Hints Configuration parameters to optimize queries Summary

8. Dealing with Large Objects

Why large objects? PostgreSQL large objects

Implementing large objects Large objects in action

Manipulating large objects through the libpq client interface library

lo_create lo_import lo_export lo_open lo_write lo_read lo_close lo_unlink

Summary

9. Communicating with PostgreSQL Using LibPQ

Connecting and disconnecting to PostgreSQL

Using PQconnectdb Using PQconnectdbParams Using PQsetdbLogin Using PQsetdb Using PQfinish Using PQreset

Establishing a connection asynchronously

Using PQconnectStartParams Using PQconnectStart Using PQconnectPoll Using PQresetStart Using PQresetPoll

Connection-supporting functions

Using PQconninfoParse Using PQpingParams

Executing a query

Using PQexec Using PQexecParams

Executing prepared statements

Using PQprepare Using PQexecPrepared

Retrieving data

Using PQntuples Using PQnfields Using PQfname Using PQfnumber Using PQftable Using PQftablecol Using PQfformat Using PQftype Using PQfmod Using PQfsize Using PQbinaryTuples Using PQgetvalue Using PQgetisnull Using PQgetlength Using PQnparams Using PQparamtype

Using status functions

Using PQresultStatus Using PQresStatus

Summary

10. Embedded SQL in C – ECPG

Writing ECPG programs Compiling an ECPG program

ECPG DECLARE sections Connection using ECPG Disconnecting using ECPG Selecting a connection

Running SQL commands

Using host variables Passing values to SQL Getting values from SQL

Dynamic SQL

Error handling

How to set an error callback

Conditions Actions

Using sqlca for error handling

Summary

11. Foreign Data Wrapper

Creating foreign data wrappers The basic components of foreign data wrappers

The C file Makefile to compile the foreign data wrapper

A SQL file to map SQL functions to C functions

The control file to manage version and module path

Loading foreign data wrappers

Creating a server Creating user mapping Creating a foreign table

Using foreign data wrappers

Working with postgres_fdw Working with file_fdw

Summary

12. Extensions

Features of an extension Creating extensions Altering extensions Dropping extensions How to check available extensions Additional supplied extensions

The adminpack extension The chkpass extension The citext extension The cube extension The dblink extension The file_fdw extension

Other available extensions Summary

Index

← Prev
Back
Next →

← Prev
Back
Next →