Book Image

PostgreSQL Server Programming

Book Image

PostgreSQL Server Programming

Overview of this book

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.

Related Content you might be interested in

Current Title:

Small book image
PostgreSQL Server Programming
Jun, 2013 | 264 pages
No titles found
Table of Contents (17 chapters)
PostgreSQL Server Programming
PostgreSQL Server Programming
Credits
Credits
About the Authors
About the Authors
About the Reviewer
About the Reviewer
www.PacktPub.com
www.PacktPub.com
Preface
Preface
Free Chapter
1
What Is a PostgreSQL Server?
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
2
Server Programming Environment
Server Programming Environment
Cost of acquisition
Availability of developers
Licensing
Predictability
Community
Procedural languages
Summary
3
Your First PL/pgSQL Function
Your First PL/pgSQL Function
Why PL/pgSQL?
Structure of a PL/pgSQL function
Conditional expressions
Returning a record
Acting on function results
Summary
4
Returning Structured Data
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
5
PL/pgSQL Trigger Functions
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
6
Debugging PL/pgSQL
Debugging PL/pgSQL
''Manual'' debugging with RAISE NOTICE
Visual debugging
Summary
7
Using Unrestricted Languages
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
8
Writing Advanced Functions in C
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
9
Scaling Your Database with PL/Proxy
Scaling Your Database with PL/Proxy
Simple single-server chat
Dealing with success – splitting tables over multiple databases
Summary
10
Publishing Your Code as PostgreSQL Extensions
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
Index
Index

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.

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.

Previous Section
Next Chapter