PostgreSQL Server Programming

Take your skills with PostgreSQL to a whole new level with this fascinating guide to server programming. A step by step approach with illuminating examples will educate you in the full range of possibilities.

PostgreSQL Server Programming

Hannu Krosing, Jim Mlodgenski, Kirk Roybal

1 customer reviews
Take your skills with PostgreSQL to a whole new level with this fascinating guide to server programming. A step by step approach with illuminating examples will educate you in the full range of possibilities.
Mapt Subscription
FREE
$29.99/m after trial
eBook
$6.00
RRP $29.99
Save 79%
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
$6.00
$49.99
$29.99p/m after trial
RRP $29.99
RRP $49.99
Subscription
eBook
Print + eBook
Start 30 Day Trial
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
 
Preview in Mapt

Book Details

ISBN 139781849516983
Paperback264 pages

Book Description

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.

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
On caching
Wrap up – why program in the server?
Summary
Chapter 2: Server Programming Environment
Cost of acquisition
Availability of developers
Licensing
Predictability
Community
Procedural languages
Summary
Chapter 3: Your First PL/pgSQL Function
Why PL/pgSQL?
Structure of a PL/pgSQL function
Conditional expressions
Returning a record
Acting on function 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
Summary of RETURN SETOF variants
Returning cursors
Other ways to work with structured data
Summary
Chapter 5: PL/pgSQL Trigger Functions
Creating the trigger function
Simple "Hey, I'm called" trigger
The audit trigger
Disallowing DELETE
Disallowing TRUNCATE
Modifying the NEW record
Immutable fields trigger
Controlling when a trigger is called
Visibility
Variables passed to the PL/pgSQL TRIGGER function
Summary
Chapter 6: Debugging PL/pgSQL
''Manual'' debugging with RAISE NOTICE
Visual debugging
Summary
Chapter 7: Using Unrestricted Languages
Are untrusted languages inferior to trusted ones?
Will untrusted languages corrupt the database?
Why untrusted?
Quick introduction to PL/Python
Thinking out of the "SQL database server" box
Summary
Chapter 8: Writing Advanced Functions in C
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
Additional resources for C
Summary
Chapter 9: Scaling Your Database with PL/Proxy
Simple single-server chat
Dealing with success – splitting tables over multiple databases
Summary
Chapter 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
Installing an extension from PGXN
Summary

What You Will Learn

  • Write functions in the built-in PL/PgSQL language or your language of choice
  • Extract data from foreign data sources
  • Add operators, data types, and other custom elements
  • Debug and code efficiently
  • Decide what machine resources your process will use
  • Create your own data types, operators, functions, aggregates, and even your own language
  • 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
On caching
Wrap up – why program in the server?
Summary
Chapter 2: Server Programming Environment
Cost of acquisition
Availability of developers
Licensing
Predictability
Community
Procedural languages
Summary
Chapter 3: Your First PL/pgSQL Function
Why PL/pgSQL?
Structure of a PL/pgSQL function
Conditional expressions
Returning a record
Acting on function 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
Summary of RETURN SETOF variants
Returning cursors
Other ways to work with structured data
Summary
Chapter 5: PL/pgSQL Trigger Functions
Creating the trigger function
Simple "Hey, I'm called" trigger
The audit trigger
Disallowing DELETE
Disallowing TRUNCATE
Modifying the NEW record
Immutable fields trigger
Controlling when a trigger is called
Visibility
Variables passed to the PL/pgSQL TRIGGER function
Summary
Chapter 6: Debugging PL/pgSQL
''Manual'' debugging with RAISE NOTICE
Visual debugging
Summary
Chapter 7: Using Unrestricted Languages
Are untrusted languages inferior to trusted ones?
Will untrusted languages corrupt the database?
Why untrusted?
Quick introduction to PL/Python
Thinking out of the "SQL database server" box
Summary
Chapter 8: Writing Advanced Functions in C
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
Additional resources for C
Summary
Chapter 9: Scaling Your Database with PL/Proxy
Simple single-server chat
Dealing with success – splitting tables over multiple databases
Summary
Chapter 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
Installing an extension from PGXN
Summary

Book Details

ISBN 139781849516983
Paperback264 pages
Read More
From 1 reviews

Read More Reviews

Recommended for You

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 Web Application Development with AngularJS Book Cover
Mastering Web Application Development with AngularJS
$ 26.99
$ 5.40
ODP.NET Developer's Guide: Oracle Database 10g Development with Visual Studio 2005 and the Oracle Data Provider for .NET Book Cover
ODP.NET Developer's Guide: Oracle Database 10g Development with Visual Studio 2005 and the Oracle Data Provider for .NET
$ 29.99
$ 21.00
PostgreSQL Replication Book Cover
PostgreSQL Replication
$ 29.99
$ 21.00
Responsive Web Design with HTML5 and CSS3 Book Cover
Responsive Web Design with HTML5 and CSS3
$ 23.99
$ 4.80