PostgreSQL的sql脚本中自动检查是否已执行

一、概述

项目开发过程中经常会出现用sql脚本修改数据库结构的情况，这时候可能发生某脚本重复执行或遗漏执行的情况。本项目早期，由于时间因素一直没来得及在sql脚本中加入自动检查是否已经执行过的功能，使用的是最原始的人工判断方式。趁现在难得有一个较宽松时间段，抓紧时间把这件事了结掉。

实现的步骤是：

• 创建一个记录库机构记录修改历史的表，其中有一项是修改历史的版本号；
• 创建一个存储过程，根据版本号判断，如果匹配不成功则抛出异常；
• 在修改的sql脚本最前面，加上\set ON_ERROR_STOP 1，然后调用该存储过程，后面才是正式的sql语句。

二、示例

创建表的sql：

create table db_update_history
(
  update_seq  int not null primary key,
  update_time timestamp not null,
  update_desc varchar(100)
);

创建存储过程的sql：

create or replace function check_version(update_seq in decimal, update_time in timestamp, update_desc in varchar) returns void as $$
declare v_curr_seq int;
begin
   select coalease(max(update_seq), 0) into v_curr_seq from db_update_history;
   if v_curr_seq +1 <> update_seq then
      raise exception '版本不匹配';
   else
      raise notice '版本匹配';
      insert into db_update_history values(update_seq, update_time, update_desc);
   end if;
exception
   when others then
   raise exception '(%)', sqlerrm;
end;
$$ language plpgsql;

修改库结构的sql脚本示例：

-- 检查此更新是否已经运行过，如果已经运行过，则退出
\set ON_ERROR_STOP 1

SELECT CHECK_VERSION(10, NOW(),  '第10次更新');

--正式的修改sql
...

三、补充说明

• 此方法只针对在sql脚本中实现自动检测，如果是在操作系统级脚本，有更为简单的方式；
• PostgreSQL缺省设置是：ON_ERROR_STOP=0，即脚本执行时遇到错误不停止；
• 在存储过程中抛出异常，与sql脚本最前面加“\set ON_ERROR_STOP 1”，二者缺一不可；
• 在项目中经实践证实有效。

如果你有更好的解决方法，烦请留言告知，本人也只是PostgreSQL新手。