Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying PostgreSQL Server Programming
  • Table Of Contents Toc
PostgreSQL Server Programming

PostgreSQL Server Programming - Second Edition

By : Usama Dar, Hannu Krosing, Jim Mlodgenski, Kirk Roybal
4.5 (8)
Buy this Book
close
close
PostgreSQL Server Programming

PostgreSQL Server Programming

4.5 (8)
By: Usama Dar, Hannu Krosing, Jim Mlodgenski, Kirk Roybal
Buy this Book

Overview of this book

This book is for moderate to advanced PostgreSQL database professionals who wish to extend PostgreSQL, utilizing the most updated features of PostgreSQL 9.4. For a better understanding of this book, familiarity with writing SQL, a basic idea of query tuning, and some coding experience in your preferred language is expected.
Table of Contents (16 chapters)
close
close
close
Preface
Icon
Preface
Icon
What this book covers
Icon
What you need for this book
Icon
Who this book is for
Icon
Conventions
Icon
Reader feedback
Icon
Customer support
Lock Free Chapter
1
1. What Is a PostgreSQL Server?
Icon
1. What Is a PostgreSQL Server?
Icon
Why program in the server?
Icon
About this book's code examples
Icon
Moving beyond simple functions
Icon
Managing related data with triggers
Icon
Auditing changes
Icon
Data cleaning
Icon
Custom sort orders
Icon
Programming best practices
Icon
Caching
Icon
Wrapping up – why program in the server?
Icon
Summary
2
2. Server Programming Environments
Icon
2. Server Programming Environments
Icon
Cost of acquisition
Icon
Availability of developers
Icon
Licensing
Icon
Predictability
Icon
Community
Icon
Procedural languages
Icon
Summary
3
3. Your First PL/pgSQL Function
Icon
3. Your First PL/pgSQL Function
Icon
Why PL/pgSQL?
Icon
The structure of a PL/pgSQL function
Icon
Conditional expressions
Icon
Returning a record
Icon
Acting on the function's results
Icon
Summary
4
4. Returning Structured Data
Icon
4. Returning Structured Data
Icon
Sets and arrays
Icon
Returning sets
Icon
Using a set returning function
Icon
Functions based on views
Icon
OUT parameters and records
Icon
A summary of the RETURN SETOF variants
Icon
Returning cursors
Icon
Other ways to work with structured data
Icon
Summary
5
5. PL/pgSQL Trigger Functions
chevron up
Icon
5. PL/pgSQL Trigger Functions
Icon
Creating the trigger function
Icon
Working on a simple "Hey, I'm called" trigger
Icon
The audit trigger
Icon
Disallowing DELETE
Icon
Disallowing TRUNCATE
Icon
Modifying the NEW record
Icon
The immutable fields trigger
Icon
Controlling when a trigger is called
Icon
Visibility
Icon
Variables passed to the PL/pgSQL TRIGGER function
Icon
Summary
6
6. PostgreSQL Event Triggers
Icon
6. PostgreSQL Event Triggers
Icon
Use cases for creating event triggers
Icon
Creating event triggers
Icon
Creating an audit trail
Icon
Preventing schema changes
Icon
A roadmap of event triggers
Icon
Summary
7
7. Debugging PL/pgSQL
Icon
7. Debugging PL/pgSQL
Icon
Manual debugging with RAISE NOTICE
Icon
Visual debugging
Icon
Summary
8
8. Using Unrestricted Languages
Icon
8. Using Unrestricted Languages
Icon
Are untrusted languages inferior to trusted ones?
Icon
Can you use untrusted languages for important functions?
Icon
Will untrusted languages corrupt the database?
Icon
Why untrusted?
Icon
Quick introduction to PL/Python
Icon
Thinking out of the "SQL database server" box
Icon
Summary
9
9. Writing Advanced Functions in C
Icon
9. Writing Advanced Functions in C
Icon
The simplest C function – return (a + b)
Icon
Adding functionality to add(int, int)
Icon
Basic guidelines for writing C code
Icon
Error reporting from C functions
Icon
Running queries and calling PostgreSQL functions
Icon
Handling records as arguments or returned values
Icon
Fast capturing of database changes
Icon
Doing something at commit/rollback
Icon
Synchronizing between backends
Icon
Writing functions in C++
Icon
Additional resources for C
Icon
Summary
10
10. Scaling Your Database with PL/Proxy
Icon
10. Scaling Your Database with PL/Proxy
Icon
Creating a simple single-server chat
Icon
Dealing with success – splitting tables over multiple databases
Icon
Connection Pooling
Icon
Summary
11
11. PL/Perl – Perl Procedural Language
Icon
11. PL/Perl – Perl Procedural Language
Icon
When to use PL/Perl
Icon
Installing PL/Perl
Icon
A simple PL/Perl function
Icon
Passing and returning non-scalar types
Icon
Writing PL/Perl triggers
Icon
Untrusted Perl
Icon
Summary
12
12. PL/Tcl – Tcl Procedural Language
Icon
12. PL/Tcl – Tcl Procedural Language
Icon
Installing PL/Tcl
Icon
A simple PL/Tcl function
Icon
Passing and returning arrays
Icon
Passing composite-type arguments
Icon
Accessing databases
Icon
Writing PL/Tcl triggers
Icon
Untrusted Tcl
Icon
Summary
13
13. Publishing Your Code as PostgreSQL Extensions
Icon
13. Publishing Your Code as PostgreSQL Extensions
Icon
When to create an extension
Icon
Unpackaged extensions
Icon
Extension versions
Icon
The .control file
Icon
Building an extension
Icon
Installing an extension
Icon
Viewing extensions
Icon
Publishing your extension
Icon
Installing an extension from PGXN
Icon
Summary
14
14. PostgreSQL as an Extensible RDBMS
Icon
14. PostgreSQL as an Extensible RDBMS
Icon
What can't be extended?
Icon
Creating a new operator
Icon
Creating index access methods
Icon
Creating user-defined aggregates
Icon
Using foreign data wrappers
Icon
Summary
15
Index
Icon
Index

The audit trigger

One of the most common uses of triggers is to log data changes to tables in a consistent and transparent manner. When creating an audit trigger, we first must decide what we want to log.

A logical set of things that can be logged are who changed the data, when the data was changed, and which operation changed the data. This information can be saved in the following table:

CREATE TABLE audit_log (
    username text, -- who did the change
    event_time_utc timestamp, -- when the event was recorded
    table_name text, -- contains schema-qualified table name
    operation text, -- INSERT, UPDATE, DELETE or TRUNCATE
    before_value json, -- the OLD tuple value
    after_value json -- the NEW tuple value
);

Here's some additional information on what we will log:

  • The username will get the SESSION_USER variable, so we know who was logged in and not which role he had potentially assumed using SET ROLE
  • event_time_utc will contain the event time converted to Coordinated Universal...
CONTINUE READING
83
Tech Concepts
36
Programming languages
73
Tech Tools
Icon Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.
Icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Icon 50+ new titles added per month and exclusive early access to books as they are being written.
PostgreSQL Server Programming
End of Section 5
Next Section
notes
bookmark Notes and Bookmarks search Search in title playlist Add to playlist font-size Font size

Change the font size

margin-width Margin width

Change margin width

day-mode Day/Sepia/Night Modes

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY

Submit Your Feedback

Modal Close icon
Modal Close icon
Modal Close icon