WITH 查詢 (通用表格運算式) #WITH 提供了一種編寫輔助陳述式以用於較大查詢的方法。 這些陳述式通常被稱為通用表格運算式或CTEs，可以被認為是定義僅存在於一個查詢的臨時表。 WITH 子句中的每個輔助陳述式都可以是 SELECT、INSERT、UPDATE、DELETE 或 MERGE；並且 WITH 子句本身附加到一個主要的陳述式，該陳述式也可以是 SELECT、INSERT、UPDATE、DELETE 或 MERGE。
SELECT 在 WITH 中 #SELECT 在 WITH 中的基本價值是將複雜的查詢分解為更簡單的部分。 一個例子是
WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
), top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
product,
SUM(quantity) AS product_units,
SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;
它僅在頂級銷售區域中顯示每個產品的銷售總額。 WITH 子句定義了兩個名為 regional_sales 和 top_regions 的輔助陳述式，其中 regional_sales 的輸出用於 top_regions，而 top_regions 的輸出用於主要的 SELECT 查詢。 這個例子可以在沒有 WITH 的情況下編寫，但我們需要兩層巢狀的子 SELECT。 這樣更容易理解。
可選的 RECURSIVE 修飾符將 WITH 從一個簡單的語法便利性變為一種可以完成標準 SQL 中無法完成的事情的功能。 使用 RECURSIVE，WITH 查詢可以引用它自己的輸出。 一個非常簡單的例子是這個查詢，用於計算從 1 到 100 的整數之和
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;
遞迴 WITH 查詢的一般形式始終是一個非遞迴項，然後是 UNION（或 UNION ALL），然後是一個遞迴項，其中只有遞迴項可以包含對查詢自身輸出的引用。 這樣的查詢執行如下
遞迴查詢評估
評估非遞迴項。 對於 UNION（但不是 UNION ALL），丟棄重複的列。 將所有剩餘的列包含在遞迴查詢的結果中，並將它們放置在一個臨時的工作表中。
只要工作表不為空，重複這些步驟
評估遞迴項，將工作表的當前內容替換為遞迴自引用。 對於 UNION（但不是 UNION ALL），丟棄重複的列和重複任何先前結果列的列。 將所有剩餘的列包含在遞迴查詢的結果中，並將它們放置在一個臨時的中間表中。
用中間表的內容替換工作表的內容，然後清空中間表。
雖然 RECURSIVE 允許遞迴地指定查詢，但在內部，此類查詢是迭代地評估的。
在上面的例子中，工作表在每個步驟中只有一個列，並且在連續的步驟中採用從 1 到 100 的值。 在第 100 步中，由於 WHERE 子句，沒有輸出，因此查詢終止。
遞迴查詢通常用於處理分層或樹狀結構的資料。 一個有用的例子是這個查詢，用於查找產品的所有直接和間接子部件，僅給出一個顯示直接包含關係的表
WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
UNION ALL
SELECT p.sub_part, p.part, p.quantity * pr.quantity
FROM included_parts pr, parts p
WHERE p.part = pr.sub_part
)
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part
當使用遞迴查詢計算樹狀結構遍歷時，您可能希望按深度優先或廣度優先順序對結果進行排序。 這可以通過計算與其他資料列並排的排序列，並使用它在最後對結果進行排序來完成。 請注意，這實際上並沒有控制查詢評估訪問列的順序； 就像在 SQL 中一樣，這總是取決於實現。 這種方法僅提供了一種方便的方法來事後對結果進行排序。
為了建立深度優先順序，我們為每個結果列計算一個到目前為止我們訪問過的列的陣列。 例如，考慮以下使用 link 欄位搜尋表 tree 的查詢
WITH RECURSIVE search_tree(id, link, data) AS (
SELECT t.id, t.link, t.data
FROM tree t
UNION ALL
SELECT t.id, t.link, t.data
FROM tree t, search_tree st
WHERE t.id = st.link
)
SELECT * FROM search_tree;
要新增深度優先排序資訊，您可以這樣寫
WITH RECURSIVE search_tree(id, link, data, path) AS ( SELECT t.id, t.link, t.data, ARRAY[t.id] FROM tree t UNION ALL SELECT t.id, t.link, t.data, path || t.id FROM tree t, search_tree st WHERE t.id = st.link ) SELECT * FROM search_tree ORDER BY path;
在一般情況下，需要使用多個欄位來識別列，請使用列的陣列。 例如，如果我們需要追蹤欄位 f1 和 f2
WITH RECURSIVE search_tree(id, link, data, path) AS ( SELECT t.id, t.link, t.data, ARRAY[ROW(t.f1, t.f2)] FROM tree t UNION ALL SELECT t.id, t.link, t.data, path || ROW(t.f1, t.f2) FROM tree t, search_tree st WHERE t.id = st.link ) SELECT * FROM search_tree ORDER BY path;
在只需要追蹤單一欄位的常見情況下，可以省略 ROW() 語法。如此一來，可以使用簡單的陣列，而不是複合類型陣列，從而提高效率。
若要建立廣度優先順序，您可以新增一個欄位來追蹤搜尋的深度，例如：
WITH RECURSIVE search_tree(id, link, data, depth) AS ( SELECT t.id, t.link, t.data, 0 FROM tree t UNION ALL SELECT t.id, t.link, t.data, depth + 1 FROM tree t, search_tree st WHERE t.id = st.link ) SELECT * FROM search_tree ORDER BY depth;
若要獲得穩定的排序，請將資料欄位新增為次要排序欄位。
遞迴查詢評估演算法會以廣度優先搜尋順序產生其輸出。然而，這是一個實作細節，依賴它可能並不合理。每一層中的列的順序當然是未定義的，因此在任何情況下都可能需要一些明確的排序。
有內建語法可以計算深度優先或廣度優先排序欄位。例如：
WITH RECURSIVE search_tree(id, link, data) AS (
SELECT t.id, t.link, t.data
FROM tree t
UNION ALL
SELECT t.id, t.link, t.data
FROM tree t, search_tree st
WHERE t.id = st.link
) SEARCH DEPTH FIRST BY id SET ordercol
SELECT * FROM search_tree ORDER BY ordercol;
WITH RECURSIVE search_tree(id, link, data) AS (
SELECT t.id, t.link, t.data
FROM tree t
UNION ALL
SELECT t.id, t.link, t.data
FROM tree t, search_tree st
WHERE t.id = st.link
) SEARCH BREADTH FIRST BY id SET ordercol
SELECT * FROM search_tree ORDER BY ordercol;
此語法會在內部展開為類似於上述手寫形式的內容。SEARCH 子句指定需要深度優先還是廣度優先搜尋、要追蹤以進行排序的欄位列表，以及包含可用於排序的結果資料的欄位名稱。該欄位將隱式新增到 CTE 的輸出列。
在使用遞迴查詢時，務必確定查詢的遞迴部分最終會不返回任何元組，否則查詢將無限迴圈。有時，使用 UNION 而不是 UNION ALL 可以透過丟棄重複先前輸出列的列來完成此操作。然而，通常迴圈不涉及完全重複的輸出列：可能需要僅檢查一個或幾個欄位，以查看是否已到達相同的點。處理這種情況的標準方法是計算已訪問值的陣列。例如，再次考慮以下查詢，該查詢使用 link 欄位搜尋表格 graph：
WITH RECURSIVE search_graph(id, link, data, depth) AS (
SELECT g.id, g.link, g.data, 0
FROM graph g
UNION ALL
SELECT g.id, g.link, g.data, sg.depth + 1
FROM graph g, search_graph sg
WHERE g.id = sg.link
)
SELECT * FROM search_graph;
如果 link 關係包含迴圈，則此查詢將迴圈。因為我們需要 “深度” 輸出，因此僅將 UNION ALL 變更為 UNION 無法消除迴圈。相反，我們需要識別在遵循特定連結路徑時是否已再次到達同一列。我們將兩個欄位 is_cycle 和 path 新增到容易迴圈的查詢：
WITH RECURSIVE search_graph(id, link, data, depth, is_cycle, path) AS ( SELECT g.id, g.link, g.data, 0, false, ARRAY[g.id] FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, g.id = ANY(path), path || g.id FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT is_cycle ) SELECT * FROM search_graph;
除了防止迴圈之外，陣列值通常本身也很有用，可以表示為到達任何特定列所採取的 “路徑”。
在一般情況下，需要檢查多個欄位才能識別迴圈，請使用列陣列。例如，如果我們需要比較欄位 f1 和 f2：
WITH RECURSIVE search_graph(id, link, data, depth, is_cycle, path) AS ( SELECT g.id, g.link, g.data, 0, false, ARRAY[ROW(g.f1, g.f2)] FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, ROW(g.f1, g.f2) = ANY(path), path || ROW(g.f1, g.f2) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT is_cycle ) SELECT * FROM search_graph;
在只需要檢查單一欄位以識別迴圈的常見情況下，可以省略 ROW() 語法。如此一來，可以使用簡單的陣列，而不是複合類型陣列，從而提高效率。
有內建語法可以簡化迴圈偵測。上面的查詢也可以這樣寫：
WITH RECURSIVE search_graph(id, link, data, depth) AS (
SELECT g.id, g.link, g.data, 1
FROM graph g
UNION ALL
SELECT g.id, g.link, g.data, sg.depth + 1
FROM graph g, search_graph sg
WHERE g.id = sg.link
) CYCLE id SET is_cycle USING path
SELECT * FROM search_graph;
它將在內部重寫為上述形式。CYCLE 子句首先指定要追蹤以進行迴圈偵測的欄位列表，然後是一個欄位名稱，用於顯示是否已偵測到迴圈，最後是另一個欄位名稱，用於追蹤路徑。迴圈和路徑欄位將隱式新增到 CTE 的輸出列。
迴圈路徑欄位的計算方式與上一節中顯示的深度優先排序欄位相同。一個查詢可以同時具有 SEARCH 和 CYCLE 子句，但是深度優先搜尋規範和迴圈偵測規範會產生冗餘計算，因此僅使用 CYCLE 子句並按路徑欄位排序更有效。如果需要廣度優先排序，則同時指定 SEARCH 和 CYCLE 可能很有用。
在不確定查詢是否會迴圈時，測試查詢的一個有用的技巧是在父查詢中放置 LIMIT。例如，如果沒有 LIMIT，則此查詢將永遠迴圈：
WITH RECURSIVE t(n) AS (
SELECT 1
UNION ALL
SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;
這是因為 PostgreSQL 的實作僅評估父查詢實際提取的 WITH 查詢的列數。不建議在生產環境中使用此技巧，因為其他系統的工作方式可能不同。此外，如果外部查詢對遞迴查詢的結果進行排序或將它們連接到其他表格，則它通常不起作用，因為在這種情況下，外部查詢通常會嘗試提取 WITH 查詢的所有輸出。
WITH 查詢的一個有用特性是，即使它們被父查詢或同級 WITH 查詢多次引用，它們通常也只會針對父查詢的每次執行評估一次。因此，可以在 WITH 查詢中放置多個地方需要使用的昂貴計算，以避免冗餘工作。另一個可能的應用是防止對具有副作用的函式進行不必要的多重評估。然而，另一方面，最佳化器無法將父查詢的限制下推到多重引用的 WITH 查詢中，因為這可能會影響 WITH 查詢輸出的所有用途，而它只應影響一個用途。多重引用的 WITH 查詢將按原樣評估，而不會抑制父查詢之後可能丟棄的列。（但是，如上所述，如果對查詢的引用僅需要有限數量的列，則評估可能會提早停止。）
然而，如果 WITH 查詢是非遞迴且沒有副作用的（也就是說，它是個不包含不穩定函數的 SELECT），那麼它可以被折疊到父查詢中，允許聯合最佳化這兩個查詢層級。預設情況下，如果父查詢僅引用 WITH 查詢一次，就會發生這種情況；但如果它多次引用 WITH 查詢，則不會發生這種情況。您可以指定 MATERIALIZED 來強制單獨計算 WITH 查詢，或者指定 NOT MATERIALIZED 來強制將其合併到父查詢中，以覆蓋此預設決策。後者有重複計算 WITH 查詢的風險，但如果 WITH 查詢的每次使用只需要 WITH 查詢的完整輸出的一小部分，那麼它仍然可以節省總體成本。
以下是這些規則的簡單範例：
WITH w AS (
SELECT * FROM big_table
)
SELECT * FROM w WHERE key = 123;
這個 WITH 查詢將被折疊，產生與以下查詢相同的執行計畫：
SELECT * FROM big_table WHERE key = 123;
特別是，如果 key 上有索引，它可能會被用於僅提取 key = 123 的那些列。另一方面，在
WITH w AS (
SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;
這個範例中，WITH 查詢將被實體化，產生 big_table 的臨時副本，然後該副本會與自身連接——而沒有任何索引的優勢。如果寫成以下形式，這個查詢的執行效率會更高：
WITH w AS NOT MATERIALIZED (
SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;
這樣父查詢的限制可以直接應用到 big_table 的掃描上。
以下是一個 NOT MATERIALIZED 可能不理想的範例：
WITH w AS (
SELECT key, very_expensive_function(val) as f FROM some_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;
在這裡，實體化 WITH 查詢可以確保 very_expensive_function 每個資料表列只會被評估一次，而不是兩次。
上面的範例僅展示了 WITH 與 SELECT 一起使用，但它也可以以相同的方式附加到 INSERT、UPDATE、DELETE 或 MERGE。在每種情況下，它實際上都提供了可以在主命令中引用的臨時資料表。
WITH 中的資料修改語句 #您可以在 WITH 中使用資料修改語句（INSERT、UPDATE、DELETE 或 MERGE）。這允許您在同一個查詢中執行多個不同的操作。一個例子是
WITH moved_rows AS (
DELETE FROM products
WHERE
"date" >= '2010-10-01' AND
"date" < '2010-11-01'
RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;
這個查詢有效地將列從 products 移到 products_log。WITH 中的 DELETE 從 products 中刪除指定的列，並透過其 RETURNING 子句傳回其內容；然後主查詢讀取該輸出並將其插入 products_log。
上述範例的一個細節是 WITH 子句附加到 INSERT，而不是 INSERT 中的子 SELECT。這是必要的，因為資料修改語句僅允許在附加到最上層語句的 WITH 子句中。但是，普通的 WITH 可見性規則適用，因此可以從子 SELECT 引用 WITH 語句的輸出。
在 WITH 中的資料修改語句通常具有 RETURNING 子句（請參閱第 6.4 節），如上面的範例所示。是 RETURNING 子句的輸出，不是資料修改語句的目標資料表，構成了可以由查詢的其餘部分引用的臨時資料表。如果 WITH 中的資料修改語句缺少 RETURNING 子句，那麼它不會形成臨時資料表，並且不能在查詢的其餘部分中引用。儘管如此，這樣的語句仍將被執行。一個不是特別有用的例子是
WITH t AS (
DELETE FROM foo
)
DELETE FROM bar;
這個範例將從資料表 foo 和 bar 中刪除所有列。報告給用戶端的受影響列的數量將僅包括從 bar 刪除的列。
不允許在資料修改語句中進行遞迴的自我參照。在某些情況下，可以透過引用遞迴 WITH 的輸出來解決此限制，例如
WITH RECURSIVE included_parts(sub_part, part) AS (
SELECT sub_part, part FROM parts WHERE part = 'our_product'
UNION ALL
SELECT p.sub_part, p.part
FROM included_parts pr, parts p
WHERE p.part = pr.sub_part
)
DELETE FROM parts
WHERE part IN (SELECT part FROM included_parts);
這個查詢會移除產品的所有直接和間接的子零件。
在 WITH 中的資料修改語句會精確地執行一次，並且始終執行到完成，而與主查詢是否讀取它們的所有（或任何）輸出無關。請注意，這與 WITH 中 SELECT 的規則不同：如上一節所述，SELECT 的執行僅在主查詢需要其輸出時才進行。
WITH 中的子語句與彼此以及與主查詢並行執行。因此，當在 WITH 中使用資料修改語句時，實際發生的指定更新的順序是無法預測的。所有語句都使用相同的快照（請參閱第 13 章）執行，因此它們無法“看到”彼此對目標資料表的影響。這減輕了實際列更新順序不可預測性的影響，並意味著 RETURNING 資料是在不同的 WITH 子語句和主查詢之間傳達變更的唯一方式。以下是一個例子：
WITH t AS (
UPDATE products SET price = price * 1.05
RETURNING *
)
SELECT * FROM products;
外部的 SELECT 會傳回 UPDATE 操作之前的原始價格，而在
WITH t AS (
UPDATE products SET price = price * 1.05
RETURNING *
)
SELECT * FROM t;
外部的 SELECT 會傳回更新的資料。
不支援在單個語句中嘗試更新同一列兩次。只有其中一個修改會發生，但是很難（有時甚至不可能）可靠地預測哪個修改會發生。這也適用於刪除在同一個語句中已經更新的列：僅執行更新。因此，通常應避免嘗試在單個語句中修改同一列兩次。特別是避免編寫可能影響主語句或同級子語句所變更的相同列的 WITH 子語句。此類語句的效果將無法預測。
目前，在 WITH 中用作資料修改語句目標的任何資料表都不得具有條件規則、ALSO 規則或展開為多個語句的 INSTEAD 規則。
如果您在文件中發現任何不正確、與您特定功能的使用經驗不符，或需要進一步澄清的地方，請使用此表單來回報文件問題。