I’ve been playing with linux recently and really enjoying it.  Quite quickly however I began looking for a good database to use in linux.  The obvious choice if you look around the internets is mysql.  Its the easy one to use, most people use it and as such there’s a ton of documentation etc..  Well for those that know me, the easiest and simplest way is not always what I choose.  So I began to look at postgresql.  PostgreSQL has more in common with databases like Oracle or DB2 than mysql, for some this is a minus but for me that’s a definite plus.  I’ve a lot of oracle experience and skills that can be applied to postgresql quite nicely.  One thing I absolutely love in postgres & oracle is the create or replace functionality for functions.  Gone are the days of if object_id(my_function) is not null…  now I can just create or replace my_function.  That’s killer in my opinion.  So now I’ve got my database (postgresql 9.0) and its time to create some tables.  This was a bit of a dilemma for me, in my native environment (oracle or sql server) I create database change scripts that safely and reentrantly ( you can run repeatedly w/o error) create the objects that are needed in a transactional manner.  But how to do this in postgresql?  Well this post is about what I’ve figured out, and how I’m currently solving that problem.

First, take a look at my postgresql github repository.  What I wanted was a way to have controlled, scripted database changes that were transactional.  They would either entirely succeed or entirely fail and leave the database unmodified.  Also the individual script files would have to have a way of saying I am change #X and I need change #Y to be present first.

Below is how I’ve accomplished this.  What this does is it applies database change 1.0.0 to a database that is of change 0.0.0  (see dbc_0.0.0 in the github rep for how schemaversion, and the initial record get put in place).  Hopefully this is of some help to someone.

create or replace function dbc_1_0_0() returns void as
$$
declare
	_old_major integer := 0;
	_old_minor integer := 0;
	_old_revision integer := 0;
	_major integer := 1;
	_minor integer := 0;
	_revision integer := 0;
	_schemaname varchar := 'my-application-name';
begin
	if exists(select 1 from schemaversion where major = _old_major and minor = _old_minor and revision = _old_revision and schemaname = _schemaname and current_version = true) then
		create sequence user_id_seq;
		create table users
		(
			userid int8 default nextval('user_id_seq') not null,
			email varchar(64) not null,
			password varchar(128) not null,
			fullname varchar(64) not null,
			created_date timestamptz not null,
			modified_date timestamptz not null,
			constraint pk_users_userid primary key (userid)
		);

		update schemaversion set current_version = false where major = _old_major and minor = _old_minor and revision = _old_revision and schemaname = _schemaname;
		insert into schemaversion
		(major,minor,revision,schemaname,installed_date,current_version)
		values
		(_major,_minor,_revision,_schemaname,current_timestamp, true);
	else
		select 'Missing prerequisite schema update ' || _schemaname || 'version ' || _major || '.' || _minor || '.' || _revision;
	end if;
exception
	when others then
	raise exception 'caught exception - (%) - when applying update %.%.% to % ', SQLERRM, _major,_minor,_revision,_schemaname;
end
$$
language 'plpgsql';
select dbc_1_0_0();
drop function dbc_1_0_0();