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
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
chevron up
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

Creating an audit trail

Let's now take a look at the complete example of an event trigger that creates an audit trail of some DDL commands in the database:

CREATE TABLE track_ddl
(
  event text, 
  command text, 
  ddl_time timestamptz, 
  usr text
);

CREATE OR REPLACE FUNCTION track_ddl_function()
RETURNS event_trigger
AS
$$
BEGIN
  INSERT INTO track_ddl values(tg_tag, tg_event, now(), session_user);
  RAISE NOTICE 'DDL logged';
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE EVENT TRIGGER track_ddl_event ON ddl_command_start
WHEN TAG IN ('CREATE TABLE', 'DROP TABLE', 'ALTER TABLE')
EXECUTE PROCEDURE track_ddl_function();

CREATE TABLE event_check(i int);

SELECT * FROM track_ddl;

-[ RECORD 1 ]------------------------
event    | CREATE TABLE
command  | ddl_command_start
ddl_time | 2014-04-13 16:58:40.331385
usr      | testusr

The example is actually quite simple. Here's what we have done in the example:

  1. First, we created a table where we store the audit log. The table is quite simple at...

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 Chapter 6
Next Chapter
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