postSQL使用触发器（trigger）分表

postSQL使用触发器（trigger）分表

脑子突发奇想到一个需求，需要把一张日志总表里的数据分到不同的子表内，每张子表的命名以_加年份结尾，不同年份的数据插到不同的子表下。想到postSQL的trigger分表的强大，所以这次写个demo例子，实现业务——当插入子表时，若不存在该年份的子表，则自动创建该年份的子表。

首先我们需要先创一张父表作为依赖

create table if not exists tb_info(
	id varchar,
	year varchar,
	name varchar
);

然后创建触发器函数

--创建触发器函数
create or replace function func_tri_info() returns trigger as
$$
	--这里定义变量
	declare my_tbname varchar(64);
	declare sql_str text;
begin
	--这里 || 表示拼接，NEW表示这条数据的对象
    my_tbname = TG_TABLE_NAME || '_' || NEW.year;
    sql_str = 'INSERT INTO '||my_tbname ||' SELECT $1.* ';
    EXECUTE sql_str USING NEW;
    return null;
--如果发生异常，就执行下面（一般来说抛找不到表的异常时就执行下面）
exception when undefined_table then
    begin
		--这里执行创表语句，记得INHERITS前面一定要有()!!!这是个坑。
        execute 'create table ' || my_tbname || '()INHERITS ('|| TG_TABLE_NAME || ')';
        EXECUTE sql_str USING NEW;
        return null;

    exception when others then
        EXECUTE sql_str USING NEW;
        return null;
    end;
end;
$$ language plpgsql;

接着把触发器，触发器函数和表关联起来

create trigger tri_ins_info before insert on tb_info for each row EXECUTE PROCEDURE func_tri_info();

最后我们可以测试一哈

insert into tb_info(id, year, name) values ('1','2017', '测试2017');
insert into tb_info(id, year, name) values ('2','2018', '测试2018');

执行完后我们刷新看一下数据库表，发现多了两张2017和2018的表

附录

完整的SQL语句

drop table if exists tb_info;
--先创一张父表
create table if not exists tb_info(
	id varchar,
	year varchar,
	name varchar
);

--删除以前创过的触发器和触发器函数
drop trigger if exists tri_ins_info on tb_info;
drop function if exists func_tri_info();

--创建触发器函数
create or replace function func_tri_info() returns trigger as
$$
	declare my_tbname varchar(64);
	declare my_year varchar(64);
	declare sql_str text;
begin  
    my_tbname = TG_TABLE_NAME || '_' || NEW.year;
    sql_str = 'INSERT INTO '||my_tbname ||' SELECT $1.* ';
    EXECUTE sql_str USING NEW;
    return null;

exception when undefined_table then
    begin
        execute 'create table ' || my_tbname || '()INHERITS ('|| TG_TABLE_NAME || ')';
        EXECUTE sql_str USING NEW;
        return null;

    exception when others then
        EXECUTE sql_str USING NEW;
        return null;
    end;
end;
$$ language plpgsql;

create trigger tri_ins_info BEFORE insert on tb_info for each row EXECUTE PROCEDURE func_tri_info();

insert into tb_info(id, year, name) values ('1','2017', '测试2017');
insert into tb_info(id, year, name) values ('2','2018', '测试2018');