PostgreSQL Server Programming -- Table of Contents -- PostgreSQL Server Programming -- Credits -- About the Authors -- About the Reviewer -- www.PacktPub.com -- Support files, eBooks, discount offers and more -- Why Subscribe? -- Free Access for Packt account holders -- Preface -- What this book covers -- What you need for this book -- Who this book is for -- Conventions -- Reader feedback -- Customer support -- Downloading the example code -- Errata -- Piracy -- Questions -- 1. What Is a PostgreSQL Server? -- Why program in the server? -- Using PL/pgSQL for integrity checks -- About this book's code examples -- Switching to the expanded display -- Moving beyond simple functions -- Data comparisons using operators -- Managing related data with triggers -- Auditing changes -- Data cleaning -- Custom sort orders -- Programming best practices -- KISS - keep it simple stupid -- DRY - don't repeat yourself -- YAGNI - you ain't gonna need it -- SOA - service-oriented architecture -- Type extensibility -- On caching -- Wrap up - why program in the server? -- Performance -- Ease of maintenance -- Simple ways to tighten security -- Summary -- 2. Server Programming Environment -- Cost of acquisition -- Availability of developers -- Licensing -- Predictability -- Community -- Procedural languages -- Platform compatibility -- Application design -- Databases are considered harmful -- Encapsulation -- What does PostgreSQL offer? -- Data locality -- More basics -- Transactions -- General error reporting and error handling -- User-defined functions (UDF) -- Other parameters -- More control -- Summary -- 3. Your First PL/pgSQL Function -- Why PL/pgSQL? -- Structure of a PL/pgSQL function -- Accessing function arguments -- Conditional expressions -- Loops with counters -- Looping through query results -- PERFORM versus SELECT -- Returning a record.

Acting on function results -- Summary -- 4. Returning Structured Data -- Sets and arrays -- Returning sets -- Returning a set of integers -- Using a set-returning function -- Returning rows from a function -- Functions based on views -- OUT parameters and records -- OUT parameters -- Returning records -- Using RETURNS TABLE -- Returning with no predefined structure -- Returning SETOF ANY -- Variadic argument lists -- Summary of RETURN SETOF variants -- Returning cursors -- Iterating over cursors returned from another function -- Wrap up of functions returning a cursor(s) -- Other ways to work with structured data -- Complex data types for modern world - XML and JSON -- XML data type and returning data as XML from functions -- Returning data in the JSON format -- Summary -- 5. PL/pgSQL Trigger Functions -- Creating the trigger function -- Creating the trigger -- Simple "Hey, I'm called" trigger -- The audit trigger -- Disallowing DELETE -- Disallowing TRUNCATE -- Modifying the NEW record -- Timestamping trigger -- Immutable fields trigger -- Controlling when a trigger is called -- Conditional trigger -- Trigger on specific field changes -- Visibility -- And most importantly - use triggers cautiously! -- Variables passed to the PL/pgSQL TRIGGER function -- Summary -- 6. Debugging PL/pgSQL -- ''Manual'' debugging with RAISE NOTICE -- Throwing exceptions -- Logging to a file -- Advantages of RAISE NOTICE -- Disadvantages of RAISE NOTICE -- Visual debugging -- Getting the debugger installed -- Installing pgAdmin3 -- Using the debugger -- Advantages of the debugger -- Disadvantages of the debugger -- Summary -- 7. Using Unrestricted Languages -- Are untrusted languages inferior to trusted ones? -- Will untrusted languages corrupt the database? -- Why untrusted? -- Why PL/Python? -- Quick introduction to PL/Python -- A minimal PL/Python function.

Data type conversions -- Writing simple functions in PL/Python -- A simple function -- Functions returning a record -- Table functions -- Running queries in the database -- Running simple queries -- Using prepared queries -- Caching prepared queries -- Writing trigger functions in PL/Python -- Exploring the inputs of a trigger -- A log trigger -- Constructing queries -- Handling exceptions -- Atomicity in Python -- Debugging PL/Python -- Using plpy.notice() for tracking the function's progress -- Using assert -- Redirecting sys.stdout and sys.stderr -- Thinking out of the "SQL database server" box -- Generating thumbnails when saving images -- Sending an e-mail -- Summary -- 8. Writing Advanced Functions in C -- Simplest C function - return (a + b) -- add_func.c -- Version 0 call conventions -- Makefile -- CREATE FUNCTION add(int, int) -- add_func.sql.in -- Summary for writing a C function -- Adding functionality to add(int, int) -- Smart handling of NULL arguments -- Working with any number of arguments -- Basic guidelines for writing C code -- Memory allocation -- Use palloc() and pfree() -- Zero-fill the structures -- Include files -- Public symbol names -- Error reporting from C functions -- "Error" states that are not errors -- When are messages sent to the client -- Running queries and calling PostgreSQL functions -- Sample C function using SPI -- Visibility of data changes -- More info on SPI_* functions -- Handling records as arguments or returned values -- Returning a single tuple of a complex type -- Extracting fields from an argument tuple -- Constructing a return tuple -- Interlude - what is Datum -- Returning a set of records -- 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.

Simple single-server chat -- Dealing with success - splitting tables over multiple databases -- What expansion plans work and when -- Moving to a bigger server -- Master-slave replication - moving reads to slave -- Multimaster replication -- Data partitioning across multiple servers -- Splitting the data -- PL/Proxy - the partitioning language -- Installing PL/Proxy -- PL/Proxy language syntax -- CONNECT, CLUSTER, and RUN ON -- SELECT and TARGET -- SPLIT - distributing array elements over several partitions -- Distribution of data -- Configuring PL/Proxy cluster using functions -- Configuring PL/Proxy cluster using SQL/MED -- Moving data from the single to the partitioned database -- Summary -- 10. 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 -- Introduction to the PostgreSQL Extension Network -- Signing up to publish your extension -- Creating an extension project the easy way -- Providing the metadata about the extension -- Writing your extension code -- Creating the package -- Submitting the package to PGXN -- Installing an extension from PGXN -- Summary -- Index.