Learn how to work with PostgreSQL as if you spent the last decade working on it. PostgreSQL is capable of providing you with all of the options that you have in your favourite development language and then extending that right on to the database server. With this knowledge in hand, you will be able to respond to the current demand for advanced PostgreSQL skills in a lucrative and booming market.

"PostgreSQL Server Programming" will show you that PostgreSQL is so much more than a database server. In fact, it could even be seen as an application development framework, with the added bonuses of transaction support, massive data storage, journaling, recovery and a host of other features that the PostgreSQL engine provides.

This book will take you from learning the basic parts of a PostgreSQL function, then writing them in languages other than the built-in PL/PgSQL. You will see how to create libraries of useful code, group them into even more useful components, and distribute them to the community. You will see how to extract data from a multitude of foreign data sources, and then extend PostgreSQL to do it natively. And you can do all of this in a nifty debugging interface that will allow you to do it efficiently and with reliability.

Approach

This practical guide leads you through numerous aspects of working with PostgreSQL. Step by step examples allow you to easily set up and extend PostgreSQL.

Who this book is for

"PostgreSQL Server Programming" is for moderate to advanced PostgreSQL database professionals. To get the best understanding of this book, you should have general experience in writing SQL, a basic idea of query tuning, and some coding experience in a language of your choice.

Frequently asked questions

Simply head over to the account section in settings and click on “Cancel Subscription” - it’s as simple as that. After you cancel, your membership will stay active for the remainder of the time you’ve paid for. Learn more here.

At the moment all of our mobile-responsive ePub books are available to download via the app. Most of our PDFs are also available to download and we're working on making the final remaining ones downloadable now. Learn more here.

Both plans give you full access to the library and all of Perlego’s features. The only differences are the price and subscription period: With the annual plan you’ll save around 30% compared to 12 months on the monthly plan.

We are an online textbook subscription service, where you can get access to an entire online library for less than the price of a single book per month. With over 1 million books across 1000+ topics, we’ve got you covered! Learn more here.

Look out for the read-aloud symbol on your next book to see if you can listen to it. The read-aloud tool reads text aloud for you, highlighting the text as it is being read. You can pause it, speed it up and slow it down. Learn more here.

Yes, you can access PostgreSQL Server Programming by Hannu Krosing, Jim Mlodgenski, Kirk Roybal in PDF and/or ePUB format, as well as other popular books in Computer Science & Databases. We have over one million books available in our catalogue for you to explore.

Information

Publisher

Packt Publishing

Year

2013

ISBN

9781849516983

Edition

Topic

Computer Science

Subtopic

Databases

Index

Computer Science

PostgreSQL Server Programming

Credits

About the Authors

About the Reviewer

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. What Is a PostgreSQL Server?

Why program in the server?

Using PL/pgSQL for integrity checks

About this book's code examples

Switching to the expanded display

Moving beyond simple functions

Data comparisons using operators

Managing related data with triggers

Auditing changes

Data cleaning

Custom sort orders

Programming best practices

KISS – keep it simple stupid

DRY – don't repeat yourself

YAGNI – you ain't gonna need it

SOA – service-oriented architecture

Type extensibility

On caching

Wrap up – why program in the server?

Performance

Ease of maintenance

Simple ways to tighten security

Summary

2. Server Programming Environment

Cost of acquisition

Availability of developers

Licensing

Predictability

Community

Procedural languages

Platform compatibility

Application design

Databases are considered harmful

Encapsulation

What does PostgreSQL offer?

Data locality

More basics

Transactions

General error reporting and error handling

User-defined functions (UDF)

Other parameters

More control

Summary

3. Your First PL/pgSQL Function

Why PL/pgSQL?

Structure of a PL/pgSQL function

Accessing function arguments

Conditional expressions

Loops with counters

Looping through query results

PERFORM versus SELECT

Returning a record

Acting on function results

Summary

4. Returning Structured Data

Sets and arrays

Returning sets

Returning a set of integers

Using a set-returning function

Returning rows from a function

Functions based on views

OUT parameters and records

OUT parameters

Returning records

Using RETURNS TABLE

Returning with no predefined structure

Returning SETOF ANY

Variadic argument lists

Summary of RETURN SETOF variants

Returning cursors

Iterating over cursors returned from another function

Wrap up of functions returning a cursor(s)

Other ways to work with structured data

Complex data types for modern world – XML and JSON

XML data type and returning data as XML from functions

Returning data in the JSON format

Summary

5. PL/pgSQL Trigger Functions

Creating the trigger function

Creating the trigger

Simple "Hey, I'm called" trigger

The audit trigger

Disallowing DELETE

Disallowing TRUNCATE

Modifying the NEW record

Timestamping trigger

Immutable fields trigger

Controlling when a trigger is called

Conditional trigger

Trigger on specific field changes

Visibility

And most importantly – use triggers cautiously!

Variables passed to the PL/pgSQL TRIGGER function

Summary

6. Debugging PL/pgSQL

''Manual'' debugging with RAISE NOTICE

Throwing exceptions

Logging to a file

Advantages of RAISE NOTICE

Disadvantages of RAISE NOTICE

Visual debugging

Getting the debugger installed

Installing pgAdmin3

Using the debugger

Advantages of the debugger

Disadvantages of the debugger

Summary

7. Using Unrestricted Languages

Are untrusted languages inferior to trusted ones?

Will untrusted languages corrupt the database?

Why untrusted?

Why PL/Python?

Quick introduction to PL/Python

A minimal PL/Python function

Data type conversions

Writing simple functions in PL/Python

A simple function

Functions returning a record

Table functions

Running queries in the database

Running simple queries

Using prepared queries

Caching prepared queries

Writing trigger functions in PL/Python

Exploring the inputs of a trigger

A log trigger

Constructing queries

Handling exceptions

Atomicity in Python

Debugging PL/Python

Using plpy.notice() for tracking the function's progress

Using assert

Redirecting sys.stdout and sys.stderr

Thinking out of the "SQL database server" box

Generating thumbnails when saving images

Sending an e-mail

Summary

8. Writing Advanced Functions in C

Simplest C function – return (a + b)

add_func.c

Version 0 call conventions

Makefile

CREATE FUNCTION add(int, int)

add_func.sql.in

Summary for writing a C function

Adding functionality to add(int, int)

Smart handling of NULL arguments

Working with any number of arguments

Basic guidelines for writing C code

Memory allocation

Use palloc() and pfree()

Zero-fill the structures

Include files

Public symbol names

Error reporting from C functions

"Error" states that are not errors

When are messages sent to the client

Running queries and calling PostgreSQL functions

Sample C function using SPI

Visibility of data changes

More info on SPI_* functions

Handling records as arguments or returned values

Returning a single tuple of a complex type

Extracting fields from an argument tuple

Constructing a return tuple

Interlude – what is Datum

Returning a set of records

Fast capturing of database changes

Doing something at commit/rollback

Synchronizing between backends

Additional resources for C

Summary

9. Scaling Your Database with PL/Proxy

Simple single-server chat

Dealing with success – splitting tables over multiple databases

What expansion plans work and when

Moving to a bigger server

Master-slave replication – moving reads to slave

Multimaster replication

Data partitioning across multiple servers

Splitting the data

PL/Proxy – the partitioning language

Installing PL/Proxy

PL/Proxy language syntax

CONNECT, CLUSTER, and RUN ON

SELECT and TARGET

SPLIT – distributing array elements over several partitions

Distribution of data

Configuring PL/Proxy cluster using functions

Configuring PL/Proxy cluster using SQL/MED

Moving data from the single to the partitioned database

Summary

10. Publishing Your Code as PostgreSQL Extensions

When to create an extension

Unpackaged extensions

Extension versions

The .control file

Building an extension

Installing an extension

Publishing your extension

Introduction to the PostgreSQL Extension Network

Signing up to publish your extension

Creating an extension project the easy way

Providing the metadata about the extension

Writing your extension code

Creating the package

Submitting the package to PGXN

Installing an extension from PGXN

Summary

Index

PostgreSQL Server Programming

All rights reserv...