PostgreSQL Server Programming: Extend PostgreSQL and integrate the database layer into your development framework

264 Pages·2013·4.251 MB·English

by  Hannu Krosing, Kirk Roybal, Jim Mlodgenski

www.it-ebooks.info
PostgreSQL Server
Programming
Extend PostgreSQL and integrate the database layer into
your development framework
Hannu Krosing
Jim Mlodgenski
Kirk Roybal
BIRMINGHAM - MUMBAI
www.it-ebooks.info
PostgreSQL Server Programming
Copyright © 2013 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or
transmitted in any form or by any means, without the prior written permission of the publisher,
except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the
information presented. However, the information contained in this book is sold without
warranty, either express or implied. Neither the authors, nor Packt Publishing, and its dealers
and distributors will be held liable for any damages caused or alleged to be caused directly or
indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies
and products mentioned in this book by the appropriate use of capitals. However, Packt
Publishing cannot guarantee the accuracy of this information.
First published: June 2013
Production Reference: 1180613
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham B3 2PB, UK.
ISBN 978-1-84951-698-3
www.packtpub.com
Cover Image by Hannu Krosing ([email protected])
www.it-ebooks.info
Credits
Authors Project Coordinator
Hannu Krosing Shraddha Vora
Jim Mlodgenski
Proofreader
Kirk Roybal
Joel T. Johnson
Reviewer
Indexer
Gabriele Bartolini
Priya Subramani
Acquisition Editor
Graphics
Sarah Cullington
Ronak Dhruv
Lead Technical Editor
Production Coordinator
Susmita Panda
Arvindkumar Gupta
Technical Editors
Cover Work
Veronica Fernandes
Arvindkumar Gupta
Vaibhav Pawar
Kirti Pujari
Amit Ramadas
www.it-ebooks.info
About the Authors
Hannu Krosing was a PostgreSQL user before it was rewritten to use SQL as its main
query language in 1995. So, he has both the historic perspective of its development and
almost 20 years of experience using it for solving various real-life problems.
Hannu was the first Database Administrator and Database Architect at Skype, where he
invented the sharding language PL/Proxy that allows scaling the user database to work
with billions of users.
Since leaving Skype at the end of 2006—about a year after it was bought up by eBay—Hannu
has been working as a PostgreSQL consultant with 2ndQuadrant, the premier PostgreSQL
consultancy with global reach and local presence in most of the world.
Hannu has co-authored another Packt Publishing book, PostgreSQL 9 Administration
Cookbook, together with one of the main PostgreSQL developers, Simon Riggs.
I want to sincerely thank my wife Evelyn for her support while writing this
book.
Jim Mlodgenski is the CTO of OpenSCG, a professional services company focused on
leveraging open source technologies for strategic advantage. He was formerly the CEO of
StormDB, a database cloud company focused on horizontal scalability. Prior to StormDB, Jim
held deeply technical roles at Cirrus Technology, Inc., EnterpriseDB, and Fusion Technologies.
Jim is also a fervent advocate of PostgreSQL. He is a member of the board of the United
States PostgreSQL Association, as well as being a part of the organizing teams of the New
York PostgreSQL User Group and Philadelphia PostgreSQL User Groups.
www.it-ebooks.info
Kirk Roybal has been active in the PostgreSQL community since 1998. He has helped to
organize user groups in Houston, Dallas, and Bloomington, IL. He has mentored many junior
database administrators and provided cross training for senior database engineers. He has
provided solutions using PostgreSQL for reporting, business intelligence, data warehousing,
applications, and development support.
Kirk saw the value of PostgreSQL when the first small business customer asked for a web
application. At the time, competitive database products were either extremely immature, or
cost prohibitive. Kirk has stood by the choice of PostgreSQL for many years now. His expertise
is founded on keeping up with features and capabilities as they have become available.
Writing a book has been a unique experience for me. Many people fantasize
about it, few start one, and even fewer get to publication. I am proud to be
part of a team that actually made it to the book shelf (itself an diminishing
breed). Thank you Sarah Cullington from Packt Publishing for giving me a
chance to participate in the project. I imagine that the PostgreSQL community
will be better served by this information, and I hope that they receive this as a
reward for the time that they have invested in me over the years.
A book only has the value that the readers give it. Thank you to the
PostgreSQL community for all of the technical, personal, and professional
development help you have given me. The PostgreSQL community is a
great bunch of people, and I have enjoyed the company of many of them.
I hope to contribute more to this project in the future, and I hope you find
my contributions as valuable as I find yours.
Thank you to my family. Firstly, for giving me a reason to succeed. Also,
thank you for listening to the gobbledygook and nodding appreciatively.
Have you ever had your family ask you what you were doing, and answered
with a function? Try it. No, then again, don't try it. They may just have you
involuntarily checked in somewhere.
www.it-ebooks.info
About the Reviewer
Gabriele Bartolini has been a long time open-source programmer and has been writing
Linux/Unix applications in C and C++ for over 10 years, specializing in search engines and
web analytics with large databases.
Gabriele has a degree in Statistics from the University of Florence. His areas of expertise are
data mining and data warehousing, having worked on web traffic analysis in Australia and Italy.
Gabriele is a consultant with 2ndQuadrant and an active member of the international
PostgreSQL community.
Gabriele currently lives in Prato, a small but vibrant city located in the northern part of
Tuscany, Italy. His second home is Melbourne, Australia, where he has studied at Monash
University and worked in the ICT sector.
His hobbies include calcio (football or soccer, depending on which part of the world you come
from) and playing his Fender Stratocaster electric guitar.
Thanks to my family, in particular Cathy who encourages always something
new to learn.
www.it-ebooks.info
www.PacktPub.com
Support files, eBooks, discount offers and more
You might want to visit www.PacktPub.com for support files and downloads related to
your book.
Did you know that Packt offers eBook versions of every book published, with PDF and ePub
files available? You can upgrade to the eBook version at www.PacktPub.com and as a print
book customer, you are entitled to a discount on the eBook copy. Get in touch with us at
[email protected] for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up
for a range of free newsletters and receive exclusive discounts and offers on Packt books
and eBooks.
http://PacktLib.PacktPub.com
Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book
library. Here, you can access, read and search across Packt's entire library of books.
Why Subscribe?
f Fully searchable across every book published by Packt
f Copy and paste, print and bookmark content
f On demand and accessible via web browser
Free Access for Packt account holders
If you have an account with Packt at www.PacktPub.com, you can use this to access
PacktLib today and view nine entirely free books. Simply use your login credentials for
immediate access.
www.it-ebooks.info
www.it-ebooks.info
Table of Contents
Preface 1
Chapter 1: What Is a PostgreSQL Server? 7
Why program in the server? 9
Using PL/pgSQL for integrity checks 10
About this book's code examples 12
Switching to the expanded display 13
Moving beyond simple functions 14
Data comparisons using operators 14
Managing related data with triggers 16
Auditing changes 19
Data cleaning 25
Custom sort orders 26
Programming best practices 27
KISS – keep it simple stupid 27
DRY – don't repeat yourself 28
YAGNI – you ain't gonna need it 28
SOA – service-oriented architecture 29
Type extensibility 29
On caching 30
Wrap up – why program in the server? 31
Performance 31
Ease of maintenance 32
Simple ways to tighten security 32
Summary 32
Chapter 2: Server Programming Environment 33
Cost of acquisition 34
Availability of developers 35
Licensing 36
www.it-ebooks.info