postgresql - 意外检测到表中的所有数据，使用循环 psql 将虚拟数据插入表

Question

我不小心删除了我的 Postgres 表中的大部分行（数据在我的测试环境中并不重要，但我需要一个虚拟数据插入到这些表中）。

让我们拿三张桌子，

1. MAIN_TABLE(main_table_id, main_fields)
2. ADDRESS_TABLE(address_table_id, main_table_id, address_type, other_fielsds)
3. CHAID_TABLE(chaid_table_id,main_table_id, shipping_address_id, chaild_fields)

我不小心删除了 ADDRESS_TABLE 中的大部分数据。

1. ADDRESS_TABLE 有一个来自 MAIN_TABLE 的外键，即 main_table_id。对于 MAIN_TABLE 中的每一行，ADDRESS_TABLE 中有两个条目，其中一个条目是其 address_type 是“计费/默认”，另一个条目是 address_type “shipping”。

2. CHAID_TABLE 有两个外键，一个来自 MAIN_TABLE，即 main_table_id，另一个来自 ADDRESS_TABLE，即 shipping_address_id。这个 shipping_address_id 是 ADDRESS_TABLE 的地址 id，它的 address_type 是 shipping 并且 ADDRESS_TABLE.main_table_id = CHAID_TABLE.main_table_id。

这些是我需要的东西。

1. 我需要为 MAIN_TABLE 中的每个原始地址创建两个虚拟地址条目，一个是地址类型“计费/默认”，另一个是“运输”类型。
2. 我需要将 address_table_id 插入其 ADDRESS_TABLE.main_table_id = CHAID_TABLE.main_table_id 的 CHAID_TABLE。和 addres_type =“航运”

如果第一个完成，我知道如何插入第二个，因为它是一个简单的插入查询。我猜。可以这样做，

UPDATE CHAID_TABLE 
  SET shipping_address_id = ADDRESS_TABLE.address_table_id 
FROM ADDRESS_TABLE  
WHERE ADDRESS_TABLE.main_table_id = CHAID_TABLE.main_table_id 
  AND ADDRESS_TABLE.addres_type ='shipping';

对于第一个，我可以在 psql 中使用循环，即遍历 MAIN_TABLE 中的所有条目并为每行插入两个虚拟行。但我不知道该怎么做，请帮我解决这个问题。

标签： postgresqlloopssql-updateforeign-keys

Answer 1

我希望你的解决方案是这样的，创建一个循环遍历 MAIN_TABLE 中所有行的函数，在循环内部执行你想要的操作，这里有两个插入语句，这个解决方案的一个问题是你在所有地址中都有相同的数据。

CREATE OR REPLACE FUNCTION get_all_MAIN_TABLE () RETURNS SETOF MAIN_TABLE  AS
$BODY$
DECLARE
    r MAIN_TABLE %rowtype;
BEGIN
    FOR r IN
        SELECT * FROM MAIN_TABLE 
    LOOP
        -- can do some processing here
        INSERT INTO ADDRESS_TABLE ( main_table_id, address_type, other_fielsds) 
                      VALUES('shipping', r.main_table_id,'NAME','','other_fielsds');
         INSERT INTO ADDRESS_TABLE ( main_table_id, address_type, other_fielsds) 
                      VALUES('billing/default',r.main_table_id,'NAME','','other_fielsds'); 
                           
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE plpgsql;

SELECT * FROM get_all_MAIN_TABLE ();