postgresql - cascade copy/insert

I have a question about copying rows in PostgreSQL. My table hierarchy is quite complex, where many tables are linked to each other via foreign keys. For the sake of simplicity, I will explain my question with two tables, but please bear in mind that my actual case requires a lot more complexity.

Say I have the following two tables:

table A
(
    integer identifier primary key
    ... -- other field开发者_C百科s
);

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

Say A and B hold the following rows:

A(1)

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

My question is: I would like to create a copy of a row in A such that the related rows in B are also copied into a new row. This would give:

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

Basically I am looking for a COPY/INSERT CASCADE.

Is there a neat trick to achieve this more or less automatically? Maybe by using temporary tables?

I believe that if I have to write all the INSERT INTO ... FROM ... queries myself in the correct order and stuff, I might go mental.

update

Let's answer my own question ;)

I did some try-outs with the RULE mechanisms in PostgreSQL and this is what I came up with:

First, the table definitions:

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)
);

Next, for each table, we create a function and corresponding rule which translates UPDATE into 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);

Finally, some testings:

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;

This seems to work quite fine. Any comments?

---

This will work. One thing I note you wisely avoided was DO ALSO rules on inserts and updates. DO ALSO with insert and update is pretty dangerous so avoid that at pretty much all cost.

On further reflection, however, triggers are not going to perform worse and offer fewer hard corners.