Preface

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. With proper knowledge in hand, you will be able to respond to the current demand for advanced PostgreSQL skills in a lucrative and booming market.

This book will take you from learning the basic parts of a PostgreSQL function through 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, 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.

What this book covers

Chapter 1, What Is a PostgreSQL Server?, introduces PostgreSQL's programming capabilities. It describes server programming and some of the real-world use cases that can leverage this technique.

Chapter 2, Server Programming Environment, discusses the PostgreSQL environment. It makes a case for why someone would choose to program in PostgreSQL covering some of PostgreSQL's business and technical advantages.

Chapter 3, Your First PL/pgSQL Function, introduces the PL/pgSQL stored procedure language. The basic structure of a function and some of the key building blocks are covered.

Chapter 4, Returning Structured Data, builds on the introduction to PL/pgSQL and shows how to return complex data back to an application. Several different methods are used and the pros and cons of each method is discussed.

Chapter 5, PL/pgSQL Trigger Functions, explores executing some server-side logic based on events occurring in the database. The concept of triggers is introduced and some use cases are discussed.

Chapter 6, Debugging PL/pgSQL, explores how server-side logic can be debugged. It starts with simple log-based notifications and builds to using an interactive graphical debugger.

Chapter 7, Using Unrestricted Languages, looks at writing server-side code in languages other than PL/pgSQL. It uses Python as the language of choice and covers reaching outside the database from a function.

Chapter 8, Writing Advanced Functions in C, provides an in-depth look at extending PostgreSQL with native C code. Several detailed examples are used to show the fundamental concepts of adding native PostgreSQL capabilities.

Chapter 9, Scaling your Database with PL/Proxy, covers another stored procedure language that allows PostgreSQL to expand beyond a single physical server. It discusses some techniques on how to split data to scale effectively.

Chapter 10, Publishing Your Code as PostgreSQL Extensions, discusses the PostgreSQL Extension Network and covers publishing a module out to the open source community.

What you need for this book

To follow along with the samples in this book, you will need the following software:

• Ubuntu 12.04 LTS

• PostgreSQL 9.2 Server or a newer version

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 a general experience in writing SQL, a basic idea of query tuning, and some coding experience in a language of your choice.

Conventions

In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.

Code words in text are shown as follows: "You can normally tell which type you're seeing by differences like this, whether you're seeing rows or RECORD."

A block of code is set as follows:

CREATE FUNCTION mid(varchar, integer, integer) RETURNS varchar
AS $$
BEGIN
  RETURN substring($1,$2,$3);
END;
$$LANGUAGE plpgsql;

When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:

CREATE TRIGGER disallow_pk_change
AFTER UPDATE OF id ON table_with_pk_id
FOR EACH ROWEXECUTE PROCEDURE cancel_op();

Any command-line input or output is written as follows:

hannu=# select get_new_messages('50000');

New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "Click on the link Upload a Distribution."

Note

Warnings or important notes appear in a box like this.

Tip

Tips and tricks appear like this.

Reader feedback

Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.

To send us general feedback, simply send an e-mail to <[e-mail]>, and mention the book title via the subject of your message.

If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.

Customer support

Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.

Downloading the example code

You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

Errata

Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.

Piracy

Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.

Please contact us at <[e-mail]> with a link to the suspected pirated material.

We appreciate your help in protecting our authors, and our ability to bring you valuable content.

Questions

You can contact us at <[e-mail]> if you are having a problem with any aspect of the book, and we will do our best to address it.