O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

PostgreSQL Server Programming

Book Description

Take your skills with PostgreSQL to a whole new level with this fascinating guide to server programming. A step by step approach with illuminating examples will educate you in the full range of possibilities.

  • Understand the extension framework of PostgreSQL, and leverage it in ways that you haven't even invented yet
  • Write functions, create your own data types, all in your favourite programming language
  • Step-by-step tutorial with plenty of tips and tricks to kick-start server programming

In Detail

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.

Table of Contents

  1. PostgreSQL Server Programming
    1. Table of Contents
    2. PostgreSQL Server Programming
    3. Credits
    4. About the Authors
    5. About the Reviewer
    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. On caching
      10. Wrap up – why program in the server?
        1. Performance
        2. Ease of maintenance
        3. Simple ways to tighten security
      11. Summary
    9. 2. Server Programming Environment
      1. Cost of acquisition
      2. Availability of developers
      3. Licensing
      4. Predictability
      5. Community
      6. Procedural languages
        1. Platform compatibility
        2. Application design
          1. Databases are considered harmful
          2. Encapsulation
          3. What does PostgreSQL offer?
          4. Data locality
        3. More basics
          1. Transactions
          2. General error reporting and error handling
          3. User-defined functions (UDF)
          4. Other parameters
          5. More control
      7. Summary
    10. 3. Your First PL/pgSQL Function
      1. Why PL/pgSQL?
      2. Structure of a PL/pgSQL function
        1. Accessing function arguments
      3. Conditional expressions
        1. Loops with counters
        2. Looping through query results
        3. PERFORM versus SELECT
      4. Returning a record
      5. Acting on function 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
        1. Returning rows from a 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. Summary of RETURN SETOF variants
      7. Returning cursors
        1. Iterating over cursors returned from another function
        2. Wrap up of functions returning a cursor(s)
      8. Other ways to work with structured data
        1. Complex data types for 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. Simple "Hey, I'm called" trigger
      3. The audit trigger
      4. Disallowing DELETE
      5. Disallowing TRUNCATE
      6. Modifying the NEW record
        1. Timestamping trigger
      7. Immutable fields trigger
      8. Controlling when a trigger is called
        1. Conditional trigger
        2. Trigger on specific field changes
      9. Visibility
        1. And most importantly – use triggers cautiously!
      10. Variables passed to the PL/pgSQL TRIGGER function
      11. Summary
    13. 6. Debugging PL/pgSQL
      1. ''Manual'' debugging with RAISE NOTICE
        1. Throwing exceptions
        2. Logging to a file
          1. Advantages of RAISE NOTICE
          2. Disadvantages of RAISE NOTICE
      2. Visual debugging
        1. Getting the debugger installed
        2. Installing pgAdmin3
        3. Using the debugger
          1. Advantages of the debugger
          2. Disadvantages of the debugger
      3. Summary
    14. 7. Using Unrestricted Languages
      1. Are untrusted languages inferior to trusted ones?
      2. Will untrusted languages corrupt the database?
      3. Why untrusted?
        1. Why PL/Python?
      4. 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() for tracking the function's progress
          2. Using assert
          3. Redirecting sys.stdout and sys.stderr
      5. Thinking out of the "SQL database server" box
        1. Generating thumbnails when saving images
        2. Sending an e-mail
      6. Summary
    15. 8. Writing Advanced Functions in C
      1. 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. 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. Additional resources for C
      11. Summary
    16. 9. Scaling Your Database with PL/Proxy
      1. 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. PL/Proxy language syntax
          3. CONNECT, CLUSTER, and RUN ON
          4. SELECT and TARGET
          5. SPLIT – distributing array elements over several partitions
          6. Distribution of data
          7. Configuring PL/Proxy cluster using functions
          8. Configuring PL/Proxy cluster using SQL/MED
        4. Moving data from the single to the partitioned database
      3. Summary
    17. 10. 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. Publishing your extension
        1. Introduction to the 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
      8. Installing an extension from PGXN
      9. Summary
    18. Index