Enforcing strict database DDL upgrade version requirements in Postgresql

When delivering a upgrade version of a web-based product, you often wish to provide your clients with database upgrade scripts.

As the data in the database is the most critical part of the installation, both the upgrader and the developer want to be absolutely certain that upgrades are pushed into the database in the correct order.

This is an example of how to achieve this objective using Postgresql.

BEGIN;

CREATE FUNCTION require_database_version(IN p_version INT) RETURNS boolean AS $$
DECLARE
  dv INT;
BEGIN
  IF p_version IS NULL THEN
    RAISE EXCEPTION 'require_database_version called with NULL input';
  END IF;
  SELECT version INTO STRICT dv FROM database_version;
  IF dv <> p_version THEN
    RAISE EXCEPTION 'Database version required: %. Found %.', p_version, dv;
  END IF;
  RETURN true;
END;
$$ LANGUAGE plpgsql VOLATILE;

CREATE FUNCTION set_database_version(IN p_version INT) RETURNS boolean AS $$
BEGIN
  IF p_version IS NULL THEN
    RAISE EXCEPTION 'set_database_version called with NULL input';
  END IF;
  EXECUTE 'CREATE OR REPLACE VIEW database_version AS SELECT ' || p_version || ' AS version';
  RETURN true;
END;
$$ LANGUAGE plpgsql VOLATILE;

SAVEPOINT s1;
SELECT require_database_version(4);
ROLLBACK TO s1;
SAVEPOINT s2;
SELECT set_database_version(6);
SAVEPOINT s3;
SELECT require_database_version(4);
ROLLBACK TO s3;
SELECT require_database_version(6);
ROLLBACK TO s3;
SELECT require_database_version(NULL);
ROLLBACK;