PG Functions

Writing Advanced PostgreSQL Functions

What we are going to cover

  • Function Basics
    • Procedural Languages
    • Function Behavior
    • Security
    • Syntax
    • Inline (Function) Syntax
  • Basic Example Functions
    • Functional Indexes
    • Real World Example of Functional Indexes
    • Case Insensitive Indexes
  • Advanced Functions
    • Working with View Definitions
  • Debugging Functions
    • Debugging your Function

Using R inside your Data Warehouse

We want to monitor index bloat every hour, but the data set is getting to large to handle in Excel. This is because the table is adding 100,000+ rows of data an hour. We have created a view on this data that reduces the data to one row per day, but that means that after 10 days worth of data we have hit Excel’s row limit of 1,048,576 rows and can no longer use the pivot table and graphing in Excel.

This left me with a problem of how to look at this data when I did not have an application that could deal with this much data.

In reading some presentations on Data Warehousing and Postgres, I saw people adding R into the Data Warehouse.

Using pgTap to unit test your functions and queries

pgTAP: Unit Testing for PostgreSQL

pgTAP is a suite of database functions that make it easy to write TAP-emitting unit tests in psql scripts or xUnit-style test functions. The TAP output is suitable for harvesting, analysis, and reporting by a TAP harness, such as those used in Perl applications.

    Good for:
    • Application Development
    • Schema Validation
    • xUnit-Style Testing
    • Module Development

Shadow Tables vers PGAudit

A shadow table is an table that is read and written by a trigger function and contains data similar to (in the same format as) its primary table, which is the table it's "shadowing" along with a few extra fields so that you can time travel the shadow table and know who did what and when. In this presentation we will cover traditional Shadow Tables which works with all current version of Postgresql and all cloud providers vers PGAudit which required Postgres 9.5 or newer. PGAudit provide PostgreSQL users with capability to produce audit logs often required to comply with government, financial, or ISO certifications. While both provide similar things, they are drastically different in the goals and the outputs and what you can easily do with those outputs.

  • Shadow Tables:
    • Writing a Shadow Table Function
    • Attaching the Function via a Trigger to a Normal Table
    • Time Traveling your Shadow Table
    • Capturing DDL Changes
  • PGAudit:
    • Compiling PG Audit
    • Auditing Tables
    • Auditing DDL
    • Setting who can be audited
    • Setting what can be audited
  • Pros & Cons
    • Shadow Tables
    • PGAudit
  • Cloud Providers
    • Amazon RDS Postgresql
    • Amazon Aurora Postgresql
    • Microsoft Azure Postgresql
    • Google Postgresql
  • Other Shadow Table Projects

The 3/22/2019 Presentation and Zip (sql) files are now presented online here that I gave in at the PostgresConf in New York.

Shadow Tables

Sometimes we want to have a copy of a table and know when everything happened to the original table, insert, update, delete, and truncate. This is possible to have happen automatically with a trigger function.

In these first slides, I will give you the basic function that I have given you before.

Then in the next set of slides, I will show you how to write a generic shadow function that can write multiple shadow tables.

Functional Indexes

This presentation will go over how to write functions that can be used to index fields. We receive faxes that are multi-page TIFF’s. The TIFF file name are called the raster id. We have data where we have the full path of the file name, the raster id and the raster id with page number.


We want to join across these fields quickly and this can be done with functional indexes, where the indexed value is not the data inside the field. In this case we will be indexing the value 0000/000000 from the above example.