Some say that calculated columns is bad design because it does not follow the relational model. It is true but performance can be greatly enhanced.

In this simple example, we have a table with 3 columns. The first and the second are given by the user and the third is calculated by a trigger. We add a condition : x * 0 = 0 * x = 1 (This fantasy comes from the requirements of a customer). It is done with the case expression.

Some say that calculated columns is bad design because it does not follow the relational model. It is true but performance can be greatly enhanced.

In this simple example, we add a condition : x * 0 = 0 * x = 1. It is done with the case construct.

By the way, it is always better to use serials for pk’s and fk’s. But I am working with database I migrate from Access to PostgreSQL. I would like to write a script that will add serials for those pk’s and fk’s and that will transform the initial pk’s and fk’s to unique constraints. Not so easy…

When we create a pk in PostgreSQL, an index named table_name_pkey is automatically created.

The following script gives all indexes, those created by the pk’s and the others.

Don’t forget that PostgreSQL does not create automatically an index when you create a fk. You have to do it yourself !

By the way, this script works for composite indexes thanks to the handy function array_to_string that creates of comma separated list of the column names. It works because the two first columns in the select clause are given in the order by.

By the way, when I was working on the script, I was listening one of the last masterworks of the Johann Sebastian Bach of our time : Pulse of Steve Reich. The music of Steve Reich is a good music to listen when you write programs because this music is well structured and transmits lofty feelings…

And we do a select in the schema ‘deleted’ for this table and for this audit_id.

That’s simple !

By the way, when I was working on the script, I was listening one of the last masterworks of the Johann Sebastian Bach of our time : Runner of Steve Reich. I can listen this music for hours. This music enhances the intellect and purify the soul.

We note that the delete in app_table of the record having pk = 23 has deleted also the two records of app_table_ref having fk = 23.

This design is very handy because you can add auditing on an existing database by adding a to each table : 1/ a column serial (that is not a pk) and 2/ the two triggers (after the creation of the generic function).

The applications are not touched and the referential integrity of the database is preserved.

Having for each modifications, the name of the table and the value of the audit_id, we can find, for insert and update, the rows that have changed.

means that we take every character since the beginning of the line until the first white space. And the line

cast(substring(input_line from '[0-9.]*') as ltree) as node_path

means that we take every character since the first white space until the end of the line.

Let’s have now a look on a first application of ltree using the <@ operator. This query gives all the paths of the tree :

select
ln1.node_id
,array_to_string(
array_agg(ln2.node_name order by ln2.node_path)
,'/') as full_path_name
from
ltree_node as ln1
inner join
ltree_node as ln2
on
ln1.node_path <@ ln2.node_path
group by
ln1.node_id, ln1.node_path, ln1.node_name
order by
ln1.node_id, full_path_name;

The line

ln1.node_path <@ ln2.node_path

means that the left argument is a descendant of the right one. And array_agg is an aggregate function (working with group by) transforming his arguments into an array.

The output is like that :

node_id full_path_name
------- --------------
1 The pedagogy of the Ramchal
2 The pedagogy of the Ramchal/The man
3 The pedagogy of the Ramchal/The man/His life
4 The pedagogy of the Ramchal/The man/His life/Padua
5 The pedagogy of the Ramchal/The man/His life/Padua/Yeshayah Bassan
6 The pedagogy of the Ramchal/The man/His life/Padua/Isaac Cantarini
7 The pedagogy of the Ramchal/The man/His life/Padua/Aviad Sar Shalom Basilea
8 The pedagogy of the Ramchal/The man/His life/Amsterdam
9 The pedagogy of the Ramchal/The man/His life/Amsterdam/Talmidim
10 The pedagogy of the Ramchal/The man/His life/Amsterdam/Talmidim/Marranes
11 The pedagogy of the Ramchal/The man/His life/Amsterdam/Talmidim/Modern science
12 The pedagogy of the Ramchal/The man/His life/Eretz Israel
...

We populate now the tables of RelGraph, our relational model for graphs.