In our ongoing Tour of Extensions we played with earth distance in How far is the nearest pub? then with hstore in a series about trigger, first to generalize Trigger Parameters then to enable us to Auditing Changes with Hstore. Today we are going to work with pg_trgm which is the trigrams PostgreSQL extension: its usage got seriously enhanced in recent PostgreSQL releases and it’s now a poor’s man Full Text Search engine.
In a previous article about Trigger Parameters we have been using the extension hstore in order to compute some extra field in our records, where the fields used both for the computation and for storing the results were passed in as dynamic parameters. Today we’re going to see another trigger use case for hstore: we are going to record changes made to our tuples.
Sometimes you want to compute values automatically at
INSERT time, like for
example a
duration column out of a
start and an
end column, both
timestamptz. It’s easy enough to do with a
BEFORE TRIGGER on your table.
What’s more complex is to come up with a parametrized spelling of the
trigger, where you can attach the same
stored procedure to any table even
when the column names are different from one another.
There was SQL before window functions and SQL after window functions: that’s how powerful this tool is. Being that of a deal breaker unfortunately means that it can be quite hard to grasp the feature. This article aims at making it crystal clear so that you can begin using it today and are able to reason about it and recognize cases where you want to be using window functions.
In our recent article about The Most Popular Pub Names we did have a look at how to find the pubs nearby, but didn’t compute the distance in between that pub and us. That’s because how to compute a distance given a position on the earth expressed as longitude and latitude is not that easy. Today, we are going to solve that problem nonetheless, thanks to PostgreSQL Extensions.
