postgresql - 级联复制/插入

Question

我有一个关于在 PostgreSQL 中复制行的问题。我的表层次结构非常复杂，其中许多表通过外键相互链接。为了简单起见，我将用两个表格来解释我的问题，但请记住，我的实际情况需要复杂得多。

假设我有以下两个表:

table A
(
    integer identifier primary key
    ... -- other fields
);

table B
(
    integer identifier primary key
    integer a          foreign key references A (identifier)
    ... -- other fields
);

假设 A 和 B 持有以下行:

A(1)

B(1, 1)
B(2, 1)

我的问题是:我想在 A 中创建一行的副本，以便 B 中的相关行也被复制到一个新行中。这将给出:

A(1)    -- the old row
A(2)    -- the new row

B(1, 1) -- the old row
B(2, 1) -- the old row
B(3, 2) -- the new row
B(4, 2) -- the new row

基本上我正在寻找一个COPY/INSERT CASCADE。

是否有巧妙的技巧或多或少地自动实现这一目标？也许通过使用临时表？

我相信，如果我必须以正确的顺序编写所有 INSERT INTO ... FROM ... 查询，我可能会精神错乱。

更新

让我们回答我自己的问题 ;)

我用 PostgreSQL 中的 RULE 机制做了一些尝试，这就是我想出的:

首先是表定义:

drop table if exists A cascade;
drop table if exists B cascade;

create table A
(
        identifier              serial                  not null                primary key,
        name                    varchar                 not null
);

create table B
(
        identifier              serial                  not null                primary key,
        name                    varchar                 not null,
        a                       integer                 not null                references A (identifier)
);

接下来，对于每个表，我们创建一个函数和相应的规则，将 UPDATE 转换为 INSERT。

create function A(in A, in A) returns integer as
$$
        declare
                r integer;
        begin
                -- A
                if ($1.identifier <> $2.identifier) then
                        insert into A (identifier, name) values ($2.identifier, $2.name) returning identifier into r;
                else
                        insert into A (name) values ($2.name) returning identifier into r;
                end if;

                -- B
                update B set a = r where a = $1.identifier;

                return r;
        end;
$$ language plpgsql;

create rule A as on update to A do instead select A(old, new);

create function B(in B, in B) returns integer as
$$
        declare
                r integer;
        begin
                if ($1.identifier <> $2.identifier) then
                        insert into B (identifier, name, a) values ($2.identifier, $2.name, $2.a) returning identifier into r;
                else
                        insert into B (name, a) values ($2.name, $2.a) returning identifier into r;
                end if;

                return r;
        end;
$$ language plpgsql;

create rule B as on update to B do instead select B(old, new);

最后，一些测试:

insert into A (name)    values ('test_1');
insert into B (name, a) values ('test_1_child', (select identifier from a where name = 'test_1'));

update A set name = 'test_2', identifier = identifier + 50;
update A set name = 'test_3';

select * from A, B where B.a = A.identifier;

这似乎工作得很好。有什么意见吗？

Answer 1

这会起作用。我注意到您明智地避免的一件事是 DO ALSO 规则插入和更新。做插入和更新是非常危险的，所以要不惜一切代价避免这种情况。

然而，经过进一步思考，触发器的性能并不会变差，也不会提供更少的硬角。