Build your own aggregate

Build your own aggregate

Before the release of pg_chameleon 2.0 I had to write an upgrade procedure to allow a smooth migration from the version 1.8.

An interesting challenge I faced was to determine the maximum and minimum position for the MySQL’s binary logs belonging to the same server but stored with different values in multiple sources.

The user defined aggregates, a feature pretty unique to PostgreSQL, allowed me to solve the problem in a robust and simple way.

Nuts and bolts part 4 - naming conventions and editors

Named identifiers Writing and debugging the SQL is not simple and a little bit of clarity becomes a massive help. Adding a prefix to the identifiers gives to the SQL developer a great knowledge about the database schema. This naming convention makes clear the difference between tables which are physical entities, and the views which are names for saved SQL statement. Adopting a similar approach for the column names makes the data type immediately recognisable.

Nuts and Bolts - part 3

INSERT The INSERT statement is composed by two elements, the attribute list and the values list. The lists are positional. The formatting should mark clearly the various elements of the query in order to make it simpler to spot the correspondence between the attribute and the value. After the words INSERT INTO there is one space followed by the table’s name After the table’s name there is one carriage return The attributes if present are enclosed between round brackets indented one tab from the word INSERT The attribute’s list indents one tab from the round brackets The attributes indent one tab fro the round brackets and are separated by a carriage return The word VALUES indents at the same level of INSERT and is followed by a carriage return The value’s list is surrounded by round brackets indented one tab from the word VALUE The values indent one tab fro the round brackets and are separated by a carriage return

Nuts and Bolts - part 2

WITH statements Because the WITH statement works like having a temporary table, the formatting will follow rules similar to the CREATE TABLE statement. • Between the WITH and the alias there is a tab followed by the word AS and a carriage return • The round brackets surrounding the inner query indent one tab from the alias • The inner query indents one tab from the round brackets

Nuts and bolts - part 1

Before starting with the book’s topic I want to explain how to set up an efficient environment and some good practice which can improve the code’s readability and quality. As somebody will notice these methods are completely opposite to the general code style trends. I’ll try to give the motivation for each rule. Anyway, in general because the SQL universe is a strange place this requires strange approach. In order to write and read effectively the SQL the coder should gain the capability to get a mental map between the query’s sections and the underlying logic.