ads' corner

PGSQL Phriday #007 – Triggers for tracking changes in a table

This month's #PGSQLPhriday is hosted by Lætita Avrot, and she asks about triggers.

It's beer o'clock for PostgreSQL

A couple days ago Devrim forked PostgreSQL to get a beer. That effort is of course laudable, but a bit extreme. PostgreSQL is very extensible, and allows to implement a beer now clock with just a few easy steps.

Advent of Code 2020: "Seating System" - Day 11

Your career as a hacker brings you more and more unreasonable tasks. Today you arrive at the ferry station, head to your next gate (no one mentioned if you even got an ice cream), and figure out where to seat. Even though you are the first person in the waiting area that does not stop you from knowing the seating habits for all other passengers, and calculate their seating patterns.


Advent of Code 2020: "Toboggan Trajectory" - Day 3

Have to admit, on first glance this challenge looks a bit complicated. It’s well suited for languages which can do string manipulations, but it’s not well suited for PostgreSQL. Earlier today I already looked at this problem together with the kid, in Python. Therefore I already knew that I have to jump multiple rows as well. To sum it up: adjust x, including overruns, jump multiple rows in y direction, count trees along the way. All in a single SQL query.

Decided to do the map search in a pl/pgSQL instead, and write a function for it.


create language if not exist

In a customer project I have to setup a database from a Makefile. Part of my problem: on Windows the installer may or may not install plpgsql into template1, therefore in consequence this language may or may not be activated in every new database. But that’s not predictable. This problem can appear on different Linux/Unix distributions too.


Ringbuffer in SQL

On a student platform we show, on every page, a random user in the upper right corner. Because we have over 11.000 users, the database has to select all matching users and sort them by random(). Thats very expensive and one of the longest running queries in the current version. And because we are currently redeveloping the whole platform, I decided to try something different for the random user. The user must not be really random, it should be enough to just display a different user every time the page is displayed. My idea was to implement a ringbuffer in SQL and forward the pointer to the next user in the buffer if we need to search the next random user.


Log Table Changes in PostgreSQL with tablelog

Ever had the need to know what happened with your data? Tablelog could be your answer.


Reverse a text in PostgreSQL

I’m always searching for a reverse function in PostgreSQL, so here is the link: