E-book details

PostgreSQL Server Programming. 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

PostgreSQL Server Programming. 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

Kirk Roybal, Jim Mlodgenski, Hannu Krosing, PostgreSQL

Ebook
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.
  • 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 books 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
  • Title: PostgreSQL Server Programming. 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
  • Author: Kirk Roybal, Jim Mlodgenski, Hannu Krosing, PostgreSQL
  • Original title: PostgreSQL Server Programming. 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.
  • ISBN: 9781849516990, 9781849516990
  • Date of issue: 2013-06-25
  • Format: Ebook
  • Item ID: e_3ay7
  • Publisher: Packt Publishing