Sometimes is useful to have columns that are automatically created using operations with other ones. Certainly this violates the Relational Model, but sometimes you don't have a choice if the database schema was given to you by a legacy system.
In my case, I found a living system with a table like this:
CREATE TABLE users ( first_name VARCHAR, family_name VARCHAR, full_name VARCHAR PRIMARY KEY );
It's certainly aberrant; the full_name
column is just a concatenation of the other two. For additional fun, it's the primary key.
PostgreSQL makes maintaining this monster easier by using triggers.
First, create the following function (this is in plpgsql
, but it could be done in any programming language linked to the PostgreSQL engine):
CREATE FUNCTION build_user_full_name_func () RETURNS trigger AS ' BEGIN NEW.full_name = NEW.first_name || '' '' || NEW.family_name; RETURN NEW; END; ' LANGUAGE plpgsql;
What this function does should be obvious: it updates full_name
by concatenating the other two columns with a space in between.
The magic is done creating this trigger:
CREATE TRIGGER build_user_full_name_trg BEFORE INSERT OR UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE build_user_full_name_func ();
Whenever users
is affected by an INSERT or UPDATE, the trigger is called, and full_name
(re)built.