Postgresql:提取一条包含所有fk引用的记录

Question

我使用一个大型postgresql生产数据库。有时需要使用实际的生产数据进行调试。由于数据库的大小，将整个转储传输到我们的开发工作站是不切实际的。

是否有一种方法，也许使用information_schema，提取单个记录，以及:

• 此记录引用的所有记录，递归级联

• 引用此记录的所有记录(先前的标准也适用于这些记录)

也许最好用一个例子来说明:

如果我们有以下表格:

People (fk to Addresses)

Addresses (fk to Cities)

城市

Employees(包括Employee_Types, People)

Employee_Types

假设我想从People表中"提取"一条特定的记录。我想要得到:

(1)《People》中的记录

(2) (1)

中address reference by the People的记录

(3)由(2)中的Addresses记录引用的Cities中的记录

(4)引用(1)

中的人员记录的任何员工记录

(5)在(4)

中找到的Employees记录所引用的Employee_Types记录

任何想法?

Answer 1

创建一个包含与这些人员相关的所有记录的转移表:

select 
    c.name as city_name, c.id as city_id,
    a.street, a.zip,
    et.type, et.type_id,
    p.id as people_id, p.name,
    e.id as employee_id, e.people_id as employee_people_id, e.type_id as employee_type_id
into transfer
from People p
inner join Addresses a on p.zip = a.zip
inner join Cities c on a.city_id = c.id
inner join Employees e on p.id = e.people_id
inner join Employee_Type et on et.id = e.type_id
where p.id in (@people_id_1, @people_id_2)

然后按照正确的顺序插入到各自的表中，以避免外键错误:

insert into Cities (name, id)
select distinct city_name, city_id
from transfer
insert into Addresses (street, zip)
select distinct street, zip
from transfer
insert into People (id, name)
select distinct people_id, name
from transfer
insert into Employee_Type (type, id)
select distinct type, type_id
from transfer
insert into Employees (id, people_id, type_id)
select distinct employee_id, employee_people_id, employee_type_id
from transfer