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

Spis treści ebooka

• 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

pokaż cały spis treści

ukryj recenzje