PostgreSQL Server Programming - Second Edition

Extend PostgreSQL using PostgreSQL server programming to create, test, debug, and optimize a range of user-defined functions in your favorite programming language
Preview in Mapt

PostgreSQL Server Programming - Second Edition

Usama Dar et al.

1 customer reviews
Extend PostgreSQL using PostgreSQL server programming to create, test, debug, and optimize a range of user-defined functions in your favorite programming language
Mapt Subscription
FREE
$29.99/m after trial
eBook
$21.00
RRP $29.99
Save 29%
Print + eBook
$49.99
RRP $49.99
What do I get with a Mapt Pro subscription?
  • Unlimited access to all Packt’s 5,000+ eBooks and Videos
  • Early Access content, Progress Tracking, and Assessments
  • 1 Free eBook or Video to download and keep every month after trial
What do I get with an eBook?
  • Download this book in EPUB, PDF, MOBI formats
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
What do I get with Print & eBook?
  • Get a paperback copy of the book delivered to you
  • Download this book in EPUB, PDF, MOBI formats
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
What do I get with a Video?
  • Download this Video course in MP4 format
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
$0.00
$21.00
$49.99
$29.99p/m after trial
RRP $29.99
RRP $49.99
Subscription
eBook
Print + eBook
Start 30 Day Trial

Frequently bought together


PostgreSQL Server Programming - Second Edition Book Cover
PostgreSQL Server Programming - Second Edition
$ 29.99
$ 21.00
PostgreSQL High Availability Cookbook - Second Edition Book Cover
PostgreSQL High Availability Cookbook - Second Edition
$ 43.99
$ 30.80
Buy 2 for $35.00
Save $38.98
Add to Cart
Subscribe and access every Packt eBook & Video.
 
  • 5,000+ eBooks & Videos
  • 50+ New titles a month
  • 1 Free eBook/Video to keep every month
Start Free Trial
 

Book Details

ISBN 139781783980581
Paperback320 pages

Book Description

This book 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.

You will get to grips with creating libraries of useful code, grouping them into even more useful components, and distributing them to the community. Then, you will take a look at user-defined functions, and how to define and utilize them efficiently. You will also learn how to extract data from a multitude of foreign data sources and extend PostgreSQL to do it natively. What's more, you can do all of this in a nifty debugging interface that will allow you to do it efficiently and reliably. This book explores all possible ways to extend PostgreSQL and write server-side code using various programming languages with concrete and easy-to-understand examples.

Table of Contents

Chapter 1: What Is a PostgreSQL Server?
Why program in the server?
About this book's code examples
Moving beyond simple functions
Managing related data with triggers
Auditing changes
Data cleaning
Custom sort orders
Programming best practices
Caching
Wrapping up – why program in the server?
Summary
Chapter 2: Server Programming Environments
Cost of acquisition
Availability of developers
Licensing
Predictability
Community
Procedural languages
Summary
Chapter 3: Your First PL/pgSQL Function
Why PL/pgSQL?
The structure of a PL/pgSQL function
Conditional expressions
Returning a record
Acting on the function's results
Summary
Chapter 4: Returning Structured Data
Sets and arrays
Returning sets
Using a set returning function
Functions based on views
OUT parameters and records
A summary of the RETURN SETOF variants
Returning cursors
Other ways to work with structured data
Summary
Chapter 5: PL/pgSQL Trigger Functions
Creating the trigger function
Working on a simple "Hey, I'm called" trigger
The audit trigger
Disallowing DELETE
Disallowing TRUNCATE
Modifying the NEW record
The immutable fields trigger
Controlling when a trigger is called
Visibility
Variables passed to the PL/pgSQL TRIGGER function
Summary
Chapter 6: PostgreSQL Event Triggers
Use cases for creating event triggers
Creating event triggers
Creating an audit trail
Preventing schema changes
A roadmap of event triggers
Summary
Chapter 7: Debugging PL/pgSQL
Manual debugging with RAISE NOTICE
Visual debugging
Summary
Chapter 8: Using Unrestricted Languages
Are untrusted languages inferior to trusted ones?
Can you use untrusted languages for important functions?
Will untrusted languages corrupt the database?
Why untrusted?
Quick introduction to PL/Python
Thinking out of the "SQL database server" box
Summary
Chapter 9: Writing Advanced Functions in C
The simplest C function – return (a + b)
Adding functionality to add(int, int)
Basic guidelines for writing C code
Error reporting from C functions
Running queries and calling PostgreSQL functions
Handling records as arguments or returned values
Fast capturing of database changes
Doing something at commit/rollback
Synchronizing between backends
Writing functions in C++
Additional resources for C
Summary
Chapter 10: Scaling Your Database with PL/Proxy
Creating a simple single-server chat
Dealing with success – splitting tables over multiple databases
Connection Pooling
Summary
Chapter 11: PL/Perl – Perl Procedural Language
When to use PL/Perl
Installing PL/Perl
A simple PL/Perl function
Passing and returning non-scalar types
Writing PL/Perl triggers
Untrusted Perl
Summary
Chapter 12: PL/Tcl – Tcl Procedural Language
Installing PL/Tcl
A simple PL/Tcl function
Passing and returning arrays
Passing composite-type arguments
Accessing databases
Writing PL/Tcl triggers
Untrusted Tcl
Summary
Chapter 13: Publishing Your Code as PostgreSQL Extensions
When to create an extension
Unpackaged extensions
Extension versions
The .control file
Building an extension
Installing an extension
Viewing extensions
Publishing your extension
Installing an extension from PGXN
Summary
Chapter 14: PostgreSQL as an Extensible RDBMS
What can't be extended?
Creating a new operator
Creating index access methods
Creating user-defined aggregates
Using foreign data wrappers
Summary

What You Will Learn

  • Write functions in the built-in PL/pgSQL language or your language of choice
  • Define and use user-defined functions (UDF)
  • Scale your data with PL/Proxy
  • Extend PostgreSQL to overcome the limitations of classical SQL servers
  • Debug using NOTIFY and using PL/pgSQL debugging extensions
  • Discover the new features in PostgreSQL 9.4 such as event triggers and other improvements relevant to server-side programming
  • Fully integrate the database layer into your development

Authors

Table of Contents

Chapter 1: What Is a PostgreSQL Server?
Why program in the server?
About this book's code examples
Moving beyond simple functions
Managing related data with triggers
Auditing changes
Data cleaning
Custom sort orders
Programming best practices
Caching
Wrapping up – why program in the server?
Summary
Chapter 2: Server Programming Environments
Cost of acquisition
Availability of developers
Licensing
Predictability
Community
Procedural languages
Summary
Chapter 3: Your First PL/pgSQL Function
Why PL/pgSQL?
The structure of a PL/pgSQL function
Conditional expressions
Returning a record
Acting on the function's results
Summary
Chapter 4: Returning Structured Data
Sets and arrays
Returning sets
Using a set returning function
Functions based on views
OUT parameters and records
A summary of the RETURN SETOF variants
Returning cursors
Other ways to work with structured data
Summary
Chapter 5: PL/pgSQL Trigger Functions
Creating the trigger function
Working on a simple "Hey, I'm called" trigger
The audit trigger
Disallowing DELETE
Disallowing TRUNCATE
Modifying the NEW record
The immutable fields trigger
Controlling when a trigger is called
Visibility
Variables passed to the PL/pgSQL TRIGGER function
Summary
Chapter 6: PostgreSQL Event Triggers
Use cases for creating event triggers
Creating event triggers
Creating an audit trail
Preventing schema changes
A roadmap of event triggers
Summary
Chapter 7: Debugging PL/pgSQL
Manual debugging with RAISE NOTICE
Visual debugging
Summary
Chapter 8: Using Unrestricted Languages
Are untrusted languages inferior to trusted ones?
Can you use untrusted languages for important functions?
Will untrusted languages corrupt the database?
Why untrusted?
Quick introduction to PL/Python
Thinking out of the "SQL database server" box
Summary
Chapter 9: Writing Advanced Functions in C
The simplest C function – return (a + b)
Adding functionality to add(int, int)
Basic guidelines for writing C code
Error reporting from C functions
Running queries and calling PostgreSQL functions
Handling records as arguments or returned values
Fast capturing of database changes
Doing something at commit/rollback
Synchronizing between backends
Writing functions in C++
Additional resources for C
Summary
Chapter 10: Scaling Your Database with PL/Proxy
Creating a simple single-server chat
Dealing with success – splitting tables over multiple databases
Connection Pooling
Summary
Chapter 11: PL/Perl – Perl Procedural Language
When to use PL/Perl
Installing PL/Perl
A simple PL/Perl function
Passing and returning non-scalar types
Writing PL/Perl triggers
Untrusted Perl
Summary
Chapter 12: PL/Tcl – Tcl Procedural Language
Installing PL/Tcl
A simple PL/Tcl function
Passing and returning arrays
Passing composite-type arguments
Accessing databases
Writing PL/Tcl triggers
Untrusted Tcl
Summary
Chapter 13: Publishing Your Code as PostgreSQL Extensions
When to create an extension
Unpackaged extensions
Extension versions
The .control file
Building an extension
Installing an extension
Viewing extensions
Publishing your extension
Installing an extension from PGXN
Summary
Chapter 14: PostgreSQL as an Extensible RDBMS
What can't be extended?
Creating a new operator
Creating index access methods
Creating user-defined aggregates
Using foreign data wrappers
Summary

Book Details

ISBN 139781783980581
Paperback320 pages
Read More
From 1 reviews

Read More Reviews

Recommended for You

PostgreSQL Server Programming Book Cover
PostgreSQL Server Programming
$ 29.99
$ 6.00
PostgreSQL 9.0 High Performance Book Cover
PostgreSQL 9.0 High Performance
$ 29.99
$ 21.00
PostgreSQL 9 Admin Cookbook Book Cover
PostgreSQL 9 Admin Cookbook
$ 29.99
$ 21.00
Mastering Object-oriented Python Book Cover
Mastering Object-oriented Python
$ 26.99
$ 18.90
PostgreSQL for Data Architects Book Cover
PostgreSQL for Data Architects
$ 39.99
$ 28.00
PostgreSQL Developer's Guide Book Cover
PostgreSQL Developer's Guide
$ 26.99
$ 18.90