且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

插入触发器最终在分区表中插入重复的行

更新时间:2023-01-22 16:40:26

一种简单的方法是创建存储过程,而不是触发器,例如add_foo([parameters]),它将确定哪个分区适合向其中插入行并返回id(或新记录值,包括id)。例如:

A simpler way is to create stored procedure instead of the triggers, for example add_foo( [parameters] ), which would decide which partition is suitable to insert a row to and return id (or the new record values, including id). For example:

CREATE OR REPLACE FUNCTION add_foo(
    _d_id   INTEGER
,   _label  VARCHAR(4)
) RETURNS BIGINT AS $$
DECLARE
    _rec    foo%ROWTYPE;
BEGIN
    _rec.id := nextval('foo_id_seq');
    _rec.d_id := _d_id;
    _rec.label := _label;
    EXECUTE 'INSERT INTO foo_' || ( _d_id % 2 ) || ' SELECT $1.*' USING _rec;
    RETURN _rec.id;
END $$ LANGUAGE plpgsql;