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

Extension versions

The version mechanism for PostgreSQL extensions is simple. Name it whatever you want and give it whatever alphanumeric version number that suits your fancy. Easy, eh? Just name the files in this format:

extension--version.sql

If you want to provide an upgrade path from one version of your extension to another, you would provide the file:

extension--oldversion--newversion.sql

This simple mechanism allows PostgreSQL to update an extension that is already in place. Gone are the days of painful exporting and re-importing data just to change the definition of a data type. So, let's go ahead and update our example extension using the file postal--1.0--1.1.sql. This update is as easy as:

ALTER EXTENSION postal UPDATE TO '1.1';

Note

A note of caution: PostgreSQL does not have any concept of what your version number means. In this example, the extension was updated from Version 1.0 to 1.1 because we explicitly provided a script for that specific conversion. PostgreSQL...

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 13
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