PostgreSQL Server Programming - Second Edition

PostgreSQL Server Programming - Second Edition

by Usama Dar, Hannu Krosing, Jim Mlodgenski, Kirk Roybal
Released February 2015
Publisher(s): Packt Publishing
ISBN: 9781783980581

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Extend PostgreSQL using PostgreSQL server programming to create, test, debug, and optimize a range of user-defined functions in your favorite programming language

In Detail

This book 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.

You will get to grips with creating libraries of useful code, grouping them into even more useful components, and distributing them to the community. Then, you will take a look at user-defined functions, and how to define and utilize them efficiently. You will also learn how to extract data from a multitude of foreign data sources and extend PostgreSQL to do it natively. What's more, you can do all of this in a nifty debugging interface that will allow you to do it efficiently and reliably. This book explores all possible ways to extend PostgreSQL and write server-side code using various programming languages with concrete and easy-to-understand examples.

What You Will Learn

  • Write functions in the built-in PL/pgSQL language or your language of choice
  • Define and use user-defined functions (UDF)
  • Scale your data with PL/Proxy
  • Extend PostgreSQL to overcome the limitations of classical SQL servers
  • Debug using NOTIFY and using PL/pgSQL debugging extensions
  • Discover the new features in PostgreSQL 9.4 such as event triggers and other improvements relevant to server-side programming
  • Fully integrate the database layer into your development

Table of contents

  1. PostgreSQL Server Programming Second Edition
    1. Table of Contents
    2. PostgreSQL Server Programming Second Edition
    3. Credits
    4. About the Authors
    5. About the Reviewers
    6. www.PacktPub.com
      1. Support files, eBooks, discount offers, and more
        1. Why subscribe?
        2. Free access for Packt account holders
    7. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Downloading the example code
        2. Errata
        3. Piracy
        4. Questions
    8. 1. What Is a PostgreSQL Server?
      1. Why program in the server?
        1. Using PL/pgSQL for integrity checks
      2. About this book's code examples
        1. Switching to the expanded display
      3. Moving beyond simple functions
        1. Data comparisons using operators
      4. Managing related data with triggers
      5. Auditing changes
      6. Data cleaning
      7. Custom sort orders
      8. Programming best practices
        1. KISS – keep it simple stupid
        2. DRY – don't repeat yourself
        3. YAGNI – you ain't gonna need it
        4. SOA – service-oriented architecture
        5. Type extensibility
      9. Caching
      10. Wrapping up – why program in the server?
        1. Performance
        2. Ease of maintenance
        3. Improved productivity
        4. Simple ways to tighten security
      11. Summary
    9. 2. Server Programming Environments
      1. Cost of acquisition
      2. Availability of developers
      3. Licensing
      4. Predictability
      5. Community
      6. Procedural languages
        1. Third-party tools
        2. Platform compatibility
        3. Application design
          1. Databases are considered harmful
          2. Encapsulation
          3. What does PostgreSQL offer?
          4. Data locality
        4. More basics
          1. Transactions
          2. General error reporting and error handling
          3. User-defined functions
          4. Other parameters
          5. More control
      7. Summary
    10. 3. Your First PL/pgSQL Function
      1. Why PL/pgSQL?
      2. The structure of a PL/pgSQL function
        1. Accessing function arguments
      3. Conditional expressions
        1. Loops with counters
          1. Statement termination
        2. Looping through query results
        3. PERFORM versus SELECT
        4. Looping Through Arrays
      4. Returning a record
      5. Acting on the function's results
      6. Summary
    11. 4. Returning Structured Data
      1. Sets and arrays
      2. Returning sets
        1. Returning a set of integers
      3. Using a set returning function
      4. Functions based on views
      5. OUT parameters and records
        1. OUT parameters
        2. Returning records
        3. Using RETURNS TABLE
        4. Returning with no predefined structure
        5. Returning SETOF ANY
        6. Variadic argument lists
      6. A summary of the RETURN SETOF variants
      7. Returning cursors
        1. Iterating over cursors returned from another function
        2. Wrapping up of functions returning cursors
      8. Other ways to work with structured data
        1. Complex data types for the modern world – XML and JSON
        2. XML data type and returning data as XML from functions
        3. Returning data in the JSON format
      9. Summary
    12. 5. PL/pgSQL Trigger Functions
      1. Creating the trigger function
        1. Creating the trigger
      2. Working on a simple "Hey, I'm called" trigger
      3. The audit trigger
      4. Disallowing DELETE
      5. Disallowing TRUNCATE
      6. Modifying the NEW record
        1. The timestamping trigger
      7. The immutable fields trigger
      8. Controlling when a trigger is called
        1. Conditional triggers
        2. Triggers on specific field changes
      9. Visibility
        1. Most importantly – use triggers cautiously!
      10. Variables passed to the PL/pgSQL TRIGGER function
      11. Summary
    13. 6. PostgreSQL Event Triggers
      1. Use cases for creating event triggers
      2. Creating event triggers
      3. Creating an audit trail
      4. Preventing schema changes
      5. A roadmap of event triggers
      6. Summary
    14. 7. Debugging PL/pgSQL
      1. Manual debugging with RAISE NOTICE
        1. Throwing exceptions
        2. Logging to a file
          1. The advantages of RAISE NOTICE
          2. The disadvantages of RAISE NOTICE
      2. Visual debugging
        1. Installing the debugger
        2. Installing the debugger from the source
        3. Installing pgAdmin3
        4. Using the debugger
          1. The advantages of the debugger
          2. The disadvantages of the debugger
      3. Summary
    15. 8. Using Unrestricted Languages
      1. Are untrusted languages inferior to trusted ones?
      2. Can you use untrusted languages for important functions?
      3. Will untrusted languages corrupt the database?
      4. Why untrusted?
        1. Why PL/Python?
      5. Quick introduction to PL/Python
        1. A minimal PL/Python function
        2. Data type conversions
        3. Writing simple functions in PL/Python
          1. A simple function
          2. Functions returning a record
          3. Table functions
        4. Running queries in the database
          1. Running simple queries
          2. Using prepared queries
          3. Caching prepared queries
        5. Writing trigger functions in PL/Python
          1. Exploring the inputs of a trigger
          2. A log trigger
        6. Constructing queries
        7. Handling exceptions
        8. Atomicity in Python
        9. Debugging PL/Python
          1. Using plpy.notice() to track the function's progress
          2. Using assert
          3. Redirecting sys.stdout and sys.stderr
      6. Thinking out of the "SQL database server" box
        1. Generating thumbnails when saving images
        2. Sending an e-mail
        3. Listing directory contents
      7. Summary
    16. 9. Writing Advanced Functions in C
      1. The simplest C function – return (a + b)
        1. add_func.c
          1. Version 0 call conventions
        2. Makefile
        3. CREATE FUNCTION add(int, int)
        4. add_func.sql.in
        5. Summary for writing a C function
      2. Adding functionality to add(int, int)
        1. Smart handling of NULL arguments
        2. Working with any number of arguments
      3. Basic guidelines for writing C code
        1. Memory allocation
          1. Use palloc() and pfree()
          2. Zero-fill the structures
          3. Include files
          4. Public symbol names
      4. Error reporting from C functions
        1. "Error" states that are not errors
        2. When are messages sent to the client?
      5. Running queries and calling PostgreSQL functions
        1. A sample C function using SPI
        2. Visibility of data changes
        3. More info on SPI_* functions
      6. Handling records as arguments or returned values
        1. Returning a single tuple of a complex type
        2. Extracting fields from an argument tuple
        3. Constructing a return tuple
        4. Interlude – what is Datum?
        5. Returning a set of records
      7. Fast capturing of database changes
      8. Doing something at commit/rollback
      9. Synchronizing between backends
      10. Writing functions in C++
      11. Additional resources for C
      12. Summary
    17. 10. Scaling Your Database with PL/Proxy
      1. Creating a simple single-server chat
      2. Dealing with success – splitting tables over multiple databases
        1. What expansion plans work and when?
          1. Moving to a bigger server
          2. Master-slave replication – moving reads to slave
          3. Multimaster replication
        2. Data partitioning across multiple servers
          1. Splitting the data
        3. PL/Proxy – the partitioning language
          1. Installing PL/Proxy
          2. The PL/Proxy language syntax
          3. CONNECT, CLUSTER, and RUN ON
          4. SELECT and TARGET
          5. SPLIT – distributing array elements over several partitions
          6. The distribution of data
          7. Configuring the PL/Proxy cluster using functions
          8. Configuring the PL/Proxy cluster using SQL/MED
        4. Moving data from the single to the partitioned database
      3. Connection Pooling
      4. Summary
    18. 11. PL/Perl – Perl Procedural Language
      1. When to use PL/Perl
      2. Installing PL/Perl
      3. A simple PL/Perl function
      4. Passing and returning non-scalar types
      5. Writing PL/Perl triggers
      6. Untrusted Perl
      7. Summary
    19. 12. PL/Tcl – Tcl Procedural Language
      1. Installing PL/Tcl
      2. A simple PL/Tcl function
        1. Null checking with Strict functions
        2. The parameter format
      3. Passing and returning arrays
      4. Passing composite-type arguments
      5. Accessing databases
      6. Writing PL/Tcl triggers
      7. Untrusted Tcl
      8. Summary
    20. 13. Publishing Your Code as PostgreSQL Extensions
      1. When to create an extension
      2. Unpackaged extensions
      3. Extension versions
      4. The .control file
      5. Building an extension
      6. Installing an extension
      7. Viewing extensions
      8. Publishing your extension
        1. Introduction to PostgreSQL Extension Network
        2. Signing up to publish your extension
        3. Creating an extension project the easy way
        4. Providing the metadata about the extension
        5. Writing your extension code
        6. Creating the package
        7. Submitting the package to PGXN
      9. Installing an extension from PGXN
      10. Summary
    21. 14. PostgreSQL as an Extensible RDBMS
      1. What can't be extended?
      2. Creating a new operator
        1. Overloading an operator
        2. Optimizing operators
          1. COMMUTATOR
          2. NEGATOR
      3. Creating index access methods
      4. Creating user-defined aggregates
      5. Using foreign data wrappers
      6. Summary
    22. Index

Product information

  • Title: PostgreSQL Server Programming - Second Edition
  • Author(s): Usama Dar, Hannu Krosing, Jim Mlodgenski, Kirk Roybal
  • Release date: February 2015
  • Publisher(s): Packt Publishing
  • ISBN: 9781783980581