我的 PostgreSQL 12 資料庫中有 2 個具有一對多關系 ( thing, 和thing_identifier) 的永久表。第二個 -- thing_identifier-- 有一個列參考thing，這樣thing_identifier可以保存給定的多個外部識別符號thing：
CREATE TABLE IF NOT EXISTS thing
(
thing_id SERIAL PRIMARY KEY,
thing_name TEXT, --this is not necessarily unique
thing_attribute TEXT --also not unique
);
CREATE TABLE IF NOT EXISTS thing_identifier
(
id SERIAL PRIMARY KEY,
thing_id integer references thing (thing_id),
identifier text
);
我需要插入一些新資料thing和thing_identifier，這兩者都來自我創建通過使用一個表COPY來一個大的CSV檔案的內容拉入資料庫，是這樣的：
CREATE TABLE IF NOT EXISTS things_to_add
(
id SERIAL PRIMARY KEY,
guid TEXT, --a unique identifier used by the supplier
thing_name TEXT, --not unique
thing_attribute TEXT --also not unique
);
樣本資料：
INSERT INTO things_to_add (guid, thing_name) VALUES
('[111-22-ABC]','Thing-a-ma-jig','pretty thing'),
('[999-88-XYZ]','Herk-a-ma-fob','blue thing');
目標是讓每一行things_to_add結果在一個新行中，每一行都在thing和 中thing_identifier，如下所示：
thing：
| thing_id | thing_name | thing attribute |
|----------|---------------------|-------------------|
| 1 | thing-a-ma-jig | pretty thing
| 2 | herk-a-ma-fob | blue thing
thing_identifier：
| id | thing_id | identifier |
|----|----------|------------------|
| 8 | 1 | '[111-22-ABC]' |
| 9 | 2 | '[999-88-XYZ]' |
我可以用一個CTEINSERT陳述句（帶RETURNING thing_id）來獲得thing_id從結果INSERT上thing，但我無法弄清楚如何讓兩者是thing_id從INSERT上thing 和原來guid的things_to_add，這需要進入thing_identifier.identifier。
為了清楚起見，唯一保證唯一的列thing是thing_id，唯一保證唯一的列things_to_add是id（我們不想存盤）和guid（這是我們想要的thing_identifier.identifier），所以沒有任何方法可以加入thing和things_to_add后INSERT上thing。
uj5u.com熱心網友回復：
您可以從以下檔案中檢索 thing_to_add.guid JOIN：
WITH list AS
(
INSERT INTO thing (thing_name)
SELECT thing_name
FROM things_to_add
RETURNING thing_id, thing_name
)
INSERT INTO thing_identifier (thing_id, identifier)
SELECT l.thing_id, t.guid
FROM list AS l
INNER JOIN thing_to_add AS t
ON l.thing_name = t.thing_name
那么，如果thing.thing_name不是唯一的，問題就更棘手了。更新兩個表thing并thing_identifier從同一個觸發器上更新thing_to_add可能會解決問題：
CREATE OR REPLACE FUNCTION after_insert_thing_to_add ()
RETURNS TRIGGER LANGUAGE sql AS
$$
WITH list AS
(
INSERT INTO thing (thing_name)
SELECT NEW.thing_name
RETURNING thing_id
)
INSERT INTO thing_identifier (thing_id, identifier)
SELECT l.thing_id, NEW.guid
FROM list AS l ;
$$
DROP TRIGGER IF EXISTS after_insert ON thing_to_add ;
CREATE TRIGGER after_insert
AFTER INSERT
ON thing_to_add
FOR EACH ROW
EXECUTE PROCEDURE after_insert_thing_to_add ();
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/351399.html
標籤：PostgreSQL sql 插入 公用表表达式 sql 返回
下一篇：如何阻止變數符號被匯出macOS