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

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.