Sign In Start Free Trial

Book Overview & Buying
Table Of Contents

PostgreSQL Server Programming - Second Edition

By : Usama Dar, Hannu Krosing, Jim Mlodgenski, Kirk Roybal

4.5 (8)

PostgreSQL Server Programming

4.5 (8)

By: Usama Dar, Hannu Krosing, Jim Mlodgenski, Kirk Roybal

Overview of this book

This book is for moderate to advanced PostgreSQL database professionals who wish to extend PostgreSQL, utilizing the most updated features of PostgreSQL 9.4. For a better understanding of this book, familiarity with writing SQL, a basic idea of query tuning, and some coding experience in your preferred language is expected.

Preface

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Free Chapter

1. What Is a PostgreSQL Server?

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

2. Server Programming Environments

2. Server Programming Environments

Cost of acquisition

Availability of developers

Licensing

Predictability

Community

Procedural languages

Summary

3. Your First PL/pgSQL Function

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

4. Returning Structured Data

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

5. PL/pgSQL Trigger Functions

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

6. PostgreSQL Event Triggers

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

7. Debugging PL/pgSQL

7. Debugging PL/pgSQL

Manual debugging with RAISE NOTICE

Visual debugging

Summary

8. Using Unrestricted Languages

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

9. Writing Advanced Functions in C

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

10. Scaling Your Database with PL/Proxy

10. Scaling Your Database with PL/Proxy

Creating a simple single-server chat

Dealing with success – splitting tables over multiple databases

Connection Pooling

Summary

11. PL/Perl – Perl Procedural Language

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

12. PL/Tcl – Tcl Procedural Language

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

13. Publishing Your Code as PostgreSQL Extensions

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

14. PostgreSQL as an Extensible RDBMS

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

Index

Index

Conventions

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

Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "If any of the checks fail, you should do ROLLBACK instead of COMMIT."

A block of code is set as follows:

CREATE TABLE accounts(owner text, balance numeric, amount numeric);
INSERT INTO accounts VALUES ('Bob',100);
INSERT INTO accounts VALUES ('Mary',200);

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 OR REPLACE FUNCTION fibonacci_seq(num integer)
  RETURNS SETOF integer AS $$
DECLARE
  a int := 0;
  b int := 1;
BEGIN
  IF (num <= 0)
    THEN RETURN;
  END IF;

  RETURN NEXT a;
  LOOP
    EXIT WHEN num <= 1;
    RETURN NEXT b;

      num = num - 1;
      SELECT b, a + b INTO a, b;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

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

$ psql -c "SELECT 1 AS test"

New terms and important words are shown in bold. Words that you see on the screen, for example, in menus or dialog boxes, appear in the text like this: "Enter some values into the columns, as seen in the preceding screenshot, and click on the Debug button."

Note

Warnings or important notes appear in a box like this.

Tip

Tips and tricks appear like this.

CONTINUE READING

83

Tech Concepts

36

Programming languages

73

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

PostgreSQL Server Programming

Search

Your notes and bookmarks