PostgreSQL 正體中文使用手冊
PostgreSQL.TW官方使用手冊小島故事加入社團
11
11
  • 簡介
  • 前言
    • 1. 什麼是PostgreSQL?
    • 2. PostgreSQL沿革
    • 3. 慣例
    • 4. 其他參考資訊
    • 5. 問題回報指南
  • I. 新手教學
    • 1. 入門指南
      • 1.1. 安裝
      • 1.2. 基礎架構
      • 1.3. 建立一個資料庫
      • 1.4. 存取一個資料庫
    • 2. SQL查詢語言
      • 2.1. 簡介
      • 2.2. 概念
      • 2.3. 創建一個新的資料表
      • 2.4. 資料列是資料表的組成單位
      • 2.5. 資料表的查詢
      • 2.6. 交叉查詢
      • 2.7. 彙總查詢
      • 2.8. 更新資料
      • 2.9. 刪除資料
    • 3. 先進功能
      • 3.1. 簡介
      • 3.2. 檢視表(View)
      • 3.3. 外部索引鍵
      • 3.4. 交易安全
      • 3.5. 窗函數
      • 3.6. 繼承
      • 3.7. 結論
  • II. SQL查詢語言
    • 4. SQL語法
      • 4.1. 語法結構
      • 4.2. 參數表示式
      • 4.3. 函數呼叫
    • 5. 定義資料結構
      • 5.1. 認識資料表
      • 5.2. 預設值
      • 5.3. 限制條件
      • 5.4. 系統欄位
      • 5.5. 表格變更
      • 5.6. 權限
      • 5.7. 資料列安全原則
      • 5.8. Schemas
      • 5.9. 繼承
      • 5.10. 分割資料表
      • 5.11. 外部資料
      • 5.12. 其他資料庫物件
      • 5.13. 相依性追蹤
    • 6. 資料處理
      • 6.1. 新增資料
      • 6.2. 更新資料
      • 6.3. 刪除資料
      • 6.4. 修改並回傳資料
    • 7. 資料查詢
      • 7.1. 概觀
      • 7.2. 資料表表示式
      • 7.3. 取得資料列表
      • 7.4. 合併查詢結果
      • 7.5. 資料排序
      • 7.6. 指定資料範圍
      • 7.7. 列舉資料
      • 7.8. 遞迴查詢(Common Table Expressions)
    • 8. 資料型別
      • 8.1. 數字型別
      • 8.2. 貨幣型別
      • 8.3. 字串型別
      • 8.4. 位元組型別(bytea)
      • 8.5. 日期時間型別
      • 8.6. 布林型別
      • 8.7. 列舉型別
      • 8.8. 地理資訊型別
      • 8.9. 網路資訊型別
      • 8.10. 位元字串型別
      • 8.11. 全文檢索型別
      • 8.12. UUID型別
      • 8.13. XML型別
      • 8.14. JSON型別
      • 8.15. 陣列
      • 8.16. 複合型別
      • 8.17. 範圍型別
      • 8.18. 指標型別
      • 8.19. pg_lsn型別
      • 8.20. 概念型別
    • 9. 函式及運算子
      • 9.1. 邏輯運算子
      • 9.2. 比較函式及運算子
      • 9.3. 數學函式及運算子
      • 9.4. 字串函式及運算子
      • 9.5. 位元字串函式及運算子
      • 9.6. 二元字串函式及運算子
      • 9.7. 特徵比對
      • 9.8. 型別轉換函式
      • 9.9 日期時間函式及運算子
      • 9.10. 列舉型別函式
      • 9.11. 地理資訊函式及運算子
      • 9.12. 網路位址函式及運算子
      • 9.13. 文字檢索函式及運算子
      • 9.14. XML函式
      • 9.15. JSON函式及運算子
      • 9.16. 序列函式
      • 9.17. 條件表示式
      • 9.18. 陣列函式及運算子
      • 9.19. 範圍函式及運算子
      • 9.20. 彙總函數
      • 9.21. Window函式
      • 9.22. 子查詢
      • 9.23. 資料列與陣列的比較運算
      • 9.24. 集合回傳函式
      • 9.25. 系統資訊函數
      • 9.26. 系統管理函式
      • 9.27. 觸發函式
      • 9.28. 事件觸發函式
    • 10. 型別轉換
      • 10.1. 概觀
      • 10.2. 運算子
      • 10.3. 函式
      • 10.4. 資料儲存轉換規則
      • 10.5. UNION、CASE 等相關結構
      • 10.6. SELECT輸出規則
    • 11. 索引(Index)
      • 11.1. 簡介
      • 11.2. 索引型別
      • 11.3. 多欄位索引
      • 11.4. 索引與ORDER BY
      • 11.5. 善用多個索引
      • 11.6. 唯一值索引
      • 11.7. 表示式索引
      • 11.8. 部份索引(partial index)
      • 11.9. 運算子物件及家族
      • 11.10. 索引與排序規則
      • 11.11. 索引限定查詢(Index-only scan)
      • 11.12. 檢查索引運用
    • 12. 全文檢索
      • 12.1. 簡介
      • 12.2. 查詢與索引
      • 12.3. 細部控制
      • 12.4. 延伸功能
      • 12.5. 斷詞
      • 12.6. 字典
      • 12.7. 組態範例
      • 12.8. 測試與除錯
      • 12.9. GIN及GiST索引型別
      • 12.10. psql支援
      • 12.11. 功能限制
    • 13. 一致性管理(MVCC)
      • 13.1. 簡介
      • 13.2. 交易隔離
      • 13.3. 鎖定模式
      • 13.4. 在應用端檢視資料一致性
      • 13.5. 特別注意
      • 13.6. 鎖定與索引
    • 14. 效能技巧
      • 14.1. 善用EXPLAIN
      • 14.2. 統計資訊
      • 14.3. 使用確切的JOIN方式
      • 14.4. 快速建立資料庫內容
      • 14.5. 彈性設定
    • 15. 平行查詢
      • 15.1. 如何運作?
      • 15.2. 啓用時機?
      • 15.3. 平行查詢計畫
      • 15.4. 平行查詢的安全性
  • III. 系統管理
    • 16. 用原始碼安裝
      • 16.1. Short Version
      • 16.2. Requirements
      • 16.3. Getting The Source
      • 16.4. 安裝流程
      • 16.5. Post-Installation Setup
      • 16.6. Supported Platforms
      • 16.7. 平台相關的注意事項
    • 17. 用原始碼在 Windows 上安裝
      • 17.1. Building with Visual C++ or the Microsoft Windows SDK
    • 18. 服務配置與維運
      • 18.1. PostgreSQL 使用者帳號
      • 18.2. Creating a Database Cluster
      • 18.3. Starting the Database Server
      • 18.4. 核心資源管理
      • 18.5. Shutting Down the Server
      • 18.6. Upgrading a PostgreSQL Cluster
      • 18.7. Preventing Server Spoofing
      • 18.8. Encryption Options
      • 18.9. Secure TCP/IP Connections with SSL
      • 18.10. Secure TCP/IP Connections with SSH Tunnels
      • 18.11. 在 Windows 註冊事件日誌
    • 19. 服務組態設定
      • 19.1. Setting Parameters
      • 19.2. File Locations
      • 19.3. 連線與認證
      • 19.4. 資源配置
      • 19.5. Write Ahead Log
      • 19.6. 複寫(Replication)
      • 19.7. 查詢規畫
      • 19.8. 錯誤回報與日誌記錄
      • 19.9. Run-time Statistics
      • 19.10. 自動資料庫清理
      • 19.11. 用戶端連線預設參數
      • 19.12. 交易鎖定管理
      • 19.13. 版本與平台的相容性
      • 19.14. Error Handling
      • 19.15. 預先配置的參數
      • 19.16. Customized Options
      • 19.17. Developer Options
      • 19.18. Short Options
    • 20. 使用者認證
      • 20.1. 設定檔:pg_hba.conf
      • 20.2. User Name Maps
      • 20.3. Authentication Methods
      • 20.4. Authentication Problems
    • 21. 資料庫角色
      • 21.1. Database Roles
      • 21.2. Role Attributes
      • 21.3. Role Membership
      • 21.4. 移除角色
      • 21.5. Default Roles
      • 21.6. Function Security
    • 22. Managing Databases
      • 22.1. Overview
      • 22.2. Creating a Database
      • 22.3. 樣版資料庫
      • 22.4. Database Configuration
      • 22.5. Destroying a Database
      • 22.6. Tablespaces
    • 23. 語系
      • 23.1. 語系支援
      • 23.2. Collation Support
      • 23.3. 字元集支援
    • 24. 例行性資料庫維護工作
      • 24.1. 例行性資料清理
      • 24.2. 定期重建索引
      • 24.3. Log File Maintenance
    • 25. 備份及還原
      • 25.1. SQL Dump
      • 25.2. File System Level Backup
      • 25.3. Continuous Archiving and Point-in-Time Recovery (PITR)
    • 26. High Availability, Load Balancing, and Replication
      • 26.1. Comparison of Different Solutions
      • 26.2. 日誌轉送備用伺服器 Log-Shipping Standby Servers
      • 26.3. Failover
      • 26.4. Alternative Method for Log Shipping
      • 26.5. Hot Standby
    • 27. Recovery Configuration
      • 27.1. Archive Recovery Settings
      • 27.2. Recovery Target Settings
      • 27.3. Standby Server Settings
    • 28. 監控資料庫活動
      • 28.1. Standard Unix Tools
      • 28.2. 統計資訊收集器
      • 28.3. Viewing Locks
      • 28.4. Progress Reporting
      • 28.5. Dynamic Tracing
    • 29. Monitoring Disk Usage
      • 29.1. Determining Disk Usage
      • 29.2. Disk Full Failure
    • 30. 高可靠度及預寫日誌
      • 30.1. Reliability
      • 30.2. Write-Ahead Logging (WAL)
      • 30.3. Asynchronous Commit
      • 30.4. WAL Configuration
      • 30.5. WAL Internals
    • 31. 邏輯複寫(Logical Replication)
      • 31.1. 發佈(Publication)
      • 31.2. 訂閱(Subscription)
      • 31.3. 衝突處理
      • 31.4. 限制
      • 31.5. 架構
      • 31.6. 監控
      • 31.7. 安全性
      • 31.8. 系統設定
      • 31.9. 快速設定
    • 32. Just-in-Time Compilation (JIT)
      • 32.1. What is JIT compilation?
      • 32.2. When to JIT?
      • 32.3. Configuration
      • 32.4. Extensibility
    • 33. 迴歸測試
      • 33.1. Running the Tests
      • 33.2. Test Evaluation
      • 33.3. Variant Comparison Files
      • 33.4. TAP Tests
      • 33.5. Test Coverage Examination
  • IV. 用戶端介面
    • 34. libpq - C Library
      • 34.1. 資料庫連線控制函數
      • 34.2. 連線狀態函數
      • 34.3. Command Execution Functions
      • 34.4. Asynchronous Command Processing
      • 34.5. Retrieving Query Results Row-By-Row
      • 34.6. Canceling Queries in Progress
      • 34.7. The Fast-Path Interface
      • 34.8. Asynchronous Notification
      • 34.9. Functions Associated with the COPY Command
      • 34.10. Control Functions
      • 34.11. Miscellaneous Functions
      • 34.12. Notice Processing
      • 34.13. Event System
      • 34.14. 環境變數
      • 34.15. 密碼檔
      • 34.16. The Connection Service File
      • 34.17. LDAP Lookup of Connection Parameters
      • 34.18. SSL Support
      • 34.19. Behavior in Threaded Programs
      • 34.20. Building libpq Programs
      • 34.21. Example Programs
    • 35. Large Objects
      • 35.1. Introduction
      • 35.2. Implementation Features
      • 35.3. Client Interfaces
      • 35.4. Server-side Functions
      • 35.5. Example Program
    • 36. ECPG - Embedded SQL in C
      • 36.1. The Concept
      • 36.2. Managing Database Connections
      • 36.3. Running SQL Commands
      • 36.4. Using Host Variables
      • 36.5. Dynamic SQL
      • 36.6. pgtypes Library
      • 36.7. Using Descriptor Areas
      • 36.8. Error Handling
      • 36.9. Preprocessor Directives
      • 36.10. Processing Embedded SQL Programs
      • 36.11. Library Functions
      • 36.12. Large Objects
      • 36.13. C++ Applications
      • 36.14. Embedded SQL Commands
      • 36.15. Informix Compatibility Mode
      • 36.16. Internals
    • 37. The Information Schema
      • 37.1. The Schema
      • 37.2. Data Types
      • 37.3. information_schema_catalog_name
      • 37.4. administrable_role_authorizations
      • 37.5. applicable_roles
      • 37.6. attributes
      • 37.7. character_sets
      • 37.8. check_constraint_routine_usage
      • 37.9. check_constraints
      • 37.10. collations
      • 37.11. collation_character_set_applicability
      • 37.12. column_domain_usage
      • 37.13. column_options
      • 37.14. column_privileges
      • 37.15. column_udt_usage
      • 37.16. columns
      • 37.17. constraint_column_usage
      • 37.18. constraint_table_usage
      • 37.19. data_type_privileges
      • 37.20. domain_constraints
      • 37.21. domain_udt_usage
      • 37.22. domains
      • 37.23. element_types
      • 37.24. enabled_roles
      • 37.25. foreign_data_wrapper_options
      • 37.26. foreign_data_wrappers
      • 37.27. foreign_server_options
      • 37.28. foreign_servers
      • 37.29. foreign_table_options
      • 37.30. foreign_tables
      • 37.31. key_column_usage
      • 37.32. parameters
      • 37.33. referential_constraints
      • 37.34. role_column_grants
      • 37.35. role_routine_grants
      • 37.36. role_table_grants
      • 37.37. role_udt_grants
      • 37.38. role_usage_grants
      • 37.39. routine_privileges
      • 37.40. routines
      • 37.41. schemata
      • 37.42. sequences
      • 37.43. sql_features
      • 37.44. sql_implementation_info
      • 37.45. sql_languages
      • 37.46. sql_packages
      • 37.47. sql_parts
      • 37.48. sql_sizing
      • 37.49. sql_sizing_profiles
      • 37.50. table_constraints
      • 37.51. table_privileges
      • 37.52. tables
      • 37.53. transforms
      • 37.54. triggered_update_columns
      • 37.55. triggers
      • 37.56. udt_privileges
      • 37.57. usage_privileges
      • 37.58. user_defined_types
      • 37.59. user_mapping_options
      • 37.60. user_mappings
      • 37.61. view_column_usage
      • 37.62. view_routine_usage
      • 37.63. view_table_usage
      • 37.64. views
  • V. 資料庫程式設計
    • 38. SQL 延伸功能
      • 38.1. How Extensibility Works
      • 38.2. The PostgreSQL Type System
      • 38.3. 使用者自訂函數
      • 38.4. User-defined Procedures
      • 38.5. Query Language (SQL) Functions
      • 38.6. Function Overloading
      • 38.7. 函數易變性類別
      • 38.8. Procedural Language Functions
      • 38.9. Internal Functions
      • 38.10. C-Language Functions
      • 38.11. User-defined Aggregates
      • 38.12. User-defined Types
      • 38.13. User-defined Operators
      • 38.14. Operator Optimization Information
      • 38.15. Interfacing Extensions To Indexes
      • 38.16. Packaging Related Objects into an Extension
      • 38.17. Extension Building Infrastructure
    • 39. Triggers
    • 40. Event Triggers
    • 41. 規則系統
      • 41.1. The Query Tree
      • 41.2. Views and the Rule System
      • 41.3. Materialized Views
      • 41.4. Rules on INSERT, UPDATE, and DELETE
      • 41.5. 規則及權限
      • 41.6. Rules and Command Status
      • 41.7. Rules Versus Triggers
    • 42. Procedural Languages(程序語言)
      • 42.1. Installing Procedural Languages
    • 43. PL/pgSQL - SQL Procedural Language
      • 43.5. 基本語法
    • 44. PL/Tcl - Tcl Procedural Language
    • 45. PL/Perl - Perl Procedural Language
    • 46. PL/Python - Python Procedural Language
    • 47. Server Programming Interface
    • 48. Background Worker Processes
    • 49. Logical Decoding
    • 50. Replication Progress Tracking
  • VI. 參考資訊
    • I. SQL 指令
      • ALTER DATABASE
      • ALTER DEFAULT PRIVILEGES
      • ALTER EXTENSION
      • ALTER FUNCTION
      • ALTER INDEX
      • ALTER LANGUAGE
      • ALTER MATERIALIZED VIEW
      • ALTER POLICY
      • ALTER PUBLICATION
      • ALTER ROLE
      • ALTER RULE
      • ALTER SCHEMA
      • ALTER SEQUENCE
      • ALTER STATISTICS
      • ALTER SUBSCRIPTION
      • ALTER TABLE
      • ALTER TABLESPACE
      • ALTER TRIGGER
      • ALTER TYPE
      • ALTER VIEW
      • ANALYZE
      • CLUSTER
      • COMMENT
      • COPY
      • CREATE CAST
      • CREATE DATABASE
      • CREATE EXTENSION
      • CREATE FOREIGN TABLE
      • CREATE FOREIGN DATA WRAPPER
      • CREATE FUNCTION
      • CREATE INDEX
      • CREATE LANGUAGE
      • CREATE MATERIALIZED VIEW
      • CREATE DOMAIN
      • CREATE POLICY
      • CREATE PROCEDURE
      • CREATE PUBLICATION
      • CREATE ROLE
      • CREATE RULE
      • CREATE SCHEMA
      • CREATE SEQUENCE
      • CREATE SERVER
      • CREATE STATISTICS
      • CREATE SUBSCRIPTION
      • CREATE TABLE
      • CREATE TABLE AS
      • CREATE TABLESPACE
      • CREATE TRANSFORM
      • CREATE TRIGGER
      • CREATE TYPE
      • CREATE USER
      • CREATE USER MAPPING
      • CREATE VIEW
      • DELETE
      • DO
      • DROP DATABASE
      • DROP EXTENSION
      • DROP FUNCTION
      • DROP INDEX
      • DROP LANGUAGE
      • DROP MATERIALIZED VIEW
      • DROP OWNED
      • DROP POLICY
      • DROP ROLE
      • DROP RULE
      • DROP SCHEMA
      • DROP SEQUENCE
      • DROP STATISTICS
      • DROP SUBSCRIPTION
      • DROP TABLE
      • DROP TABLESPACE
      • DROP TRANSFORM
      • DROP TRIGGER
      • DROP TYPE
      • DROP USER
      • DROP VIEW
      • EXECUTE
      • EXPLAIN
      • GRANT
      • IMPORT FOREIGN SCHEMA
      • INSERT
      • LISTEN
      • LOAD
      • NOTIFY
      • PREPARE TRANSACTION
      • REASSIGN OWNED
      • REFRESH MATERIALIZED VIEW
      • REINDEX
      • RESET
      • REVOKE
      • SELECT
      • SELECT INTO
      • SET
      • SET CONSTRAINTS
      • SET ROLE
      • SET SESSION AUTHORIZATION
      • SET TRANSACTION
      • SHOW
      • TRUNCATE
      • UNLISTEN
      • UPDATE
      • VACUUM
      • VALUES
    • II. PostgreSQL 用戶端工具
      • createdb
      • createuser
      • dropdb
      • dropuser
      • pgbench
      • pg_dump
      • psql
      • vacuumdb
    • III. PostgreSQL 伺服器應用程式
      • pg_test_timing
      • postgres
  • VII. 資料庫進階
    • 52. 系統目錄
      • 52.3. pg_am
      • 52.7. pg_attribute
      • 52.8. pg_authid
      • 52.9. pg_auth_members
      • 52.11 pg_class
      • 52.12. pg_collation
      • 52.13. pg_constraint
      • 52.15 pg_database
      • 52.26 pg_index
      • 52.29. pg_language
      • 52.32. pg_namespace
      • 52.33. pg_opclass
      • 52.38. pg_policy
      • 52.39. pg_proc
      • 52.44. pg_rewrite
      • 52.50. pg_statistic
      • 52.51. pg_statistic_ext
      • 52.54. pg_tablespace
      • 52.56. pg_trigger
      • 52.62. pg_type
      • 52.79. pg_replication_origin_status
      • 52.81 pg_roles
      • 52.85. pg_settings
      • 52.87. pg_stats
    • 53. Frontend/Backend Protocol
      • 53.1. Overview
      • 53.2. Message Flow
      • 53.3. SASL Authentication
      • 53.4. Streaming Replication Protocol
      • 53.5. Logical Streaming Replication Protocol
      • 53.6. Message Data Types
      • 53.7. Message Formats
      • 53.8. Error and Notice Message Fields
      • 53.9. Logical Replication Message Formats
      • 53.10. Summary of Changes since Protocol 2.0
    • 54. PostgreSQL 程式撰寫慣例
      • 54.1. Formatting
      • 54.2. Reporting Errors Within the Server
      • 54.3. Error Message Style Guide
      • 54.4. Miscellaneous Coding Conventions
    • 56. Writing A Procedural Language Handler
    • 64. GiST Indexes
      • 64.1. Introduction
      • 64.2. Built-in Operator Classes
      • 64.3. Extensibility
      • 64.4. Implementation
      • 64.5. Examples
    • 65. SP-GiST Indexes
      • 65.1. Introduction
      • 65.2. Built-in Operator Classes
      • 65.3. Extensibility
      • 65.4. Implementation
      • 65.5. Examples
    • 66. GIN 索引
      • 66.1. 簡介
      • 66.2. 內建運算子類
      • 66.3. Extensibility
      • 66.4. Implementation
      • 66.5. GIN Tips and Tricks
      • 66.6. Limitations
      • 66.7. Examples
    • 67. BRIN Indexes
      • 67.1. Introduction
      • 67.2. Built-in Operator Classes
      • 67.3. Extensibility
    • 68. 資料庫實體儲存格式
      • 68.2. TOAST
      • 68.4 可視性映射表(Visibility Map)
    • 70. How the Planner Uses Statistics
      • 70.2. Multivariate Statistics Examples
  • VIII. 附錄
    • A. PostgreSQL錯誤代碼
    • B. 日期時間格式支援
      • B.1. 日期時間解譯流程
      • B.2. 日期時間慣用字
      • B.3. 日期時間設定檔
      • B.4. 日期時間的沿革
    • C. SQL 關鍵字
    • D. SQL 相容性
    • E. 版本資訊
    • F. 延伸支援模組
      • F.4. auto_explain
      • F.11. dblink
        • dblink
      • F.33. pg_visibility
    • G. Additional Supplied Programs
      • G.1. Client Applications
        • oid2name
        • vacuumlo
      • G.2. Server Applications
        • pg_standby
    • H. 外部專案
      • H.1. 用戶端介面
      • H.2. Administration Tools
      • H.3. Procedural Languages
      • H.4. Extensions
    • I. The Source Code Repository
      • I.1. Getting The Source via Git
    • J. 文件取得
    • K. 縮寫字
  • 參考書目
Powered by GitBook
On this page
  • 7.8.1. SELECTinWITH
  • 7.8.2. WITH 中的資料修改語法

Was this helpful?

Edit on Git
Export as PDF
  1. II. SQL查詢語言
  2. 7. 資料查詢

7.8. 遞迴查詢(Common Table Expressions)

WITH 提供了一種撰寫用於更複雜查詢輔助語句的方法。這些通常被稱為公用資料表表示式或 CTE(Common Table Expressions) 的宣告可以被想成是定義僅存在於一個查詢中的臨時資料表。WITH子句中的每個輔助語句都可以是 SELECT、INSERT、UPDATE 或 DELETE;並且 WITH 子句本身附加到 SELECT、INSERT、UPDATE 或 DELETE 的主語句。

7.8.1. SELECTinWITH

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 的輔助語句,其中 top_size 使用 region_sales 的輸出,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),然後是遞迴子句,其中只有遞迴子句可以包含對查詢自己的輸出引用。這樣的查詢執行如下:

執行遞迴查詢

  1. 執行非遞迴子句。 對於 UNION(但不是 UNION ALL),丟棄重複的資料列。在遞迴查詢的結果中包含所有剩餘的資料列,並將它們放在臨時的工作資料表中。

  2. 只要工作資料表不是空的,就重複這些步驟:

    1. 執行遞迴子句,將工作資料表的當下的內容替換為遞迴自我引用。對於 UNION(但不是 UNION ALL),將重複的資料列及和之前結果重覆的資料列都丟棄。在遞迴查詢的結果中包含所有剩餘的資料列,並將其放置在臨時中介資料表中。

    2. 將工作資料表的內容替換為中介資料表的內容,然後清空中介資料表。

注意

嚴格地說,這個過程仍然是疊代的而不是遞迴的,但是 RECURSIVE 是 SQL 標準委員會所選擇的用字。

在上面的例子中,工作資料表在每一步驟中只有一個資料列,並且在連續的步驟中從 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
    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

使用遞迴查詢時,務必確保查詢的遞迴部分最終不回傳資料列,否則查詢將會無限循環。有時,使用 UNION 而不是 UNION ALL 可以透過丟棄與先前輸出行重覆的資料列來達成此目的。但是,循環查詢通常不涉及完全重複的輸出資料列:可能需要檢查一個或幾個欄位,以查看是否在之前就已經達到相同的點。處理這種情況的標準方法是從已經訪問過的陣列中,計算其值。例如,以下查詢是,使用連接欄位搜尋資料表網路圖:

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
)
SELECT * FROM search_graph;

如果連接關係包含循環,則此查詢將持續循環。因為我們需要一個「depth」輸出,只要將 UNION ALL 更改為 UNION 就不會停止循環。相反地,我們需要瞭解到,在遵循特定的連接路徑的情況下,我們是否再一次到達同樣的資料列。我們增加兩個欄位 path 和 cycle 到有循環傾向的查詢之中:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
        SELECT g.id, g.link, g.data, 1,
          ARRAY[g.id],
          false
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1,
          path || g.id,
          g.id = ANY(path)
        FROM graph g, search_graph sg
        WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

除了防止循環之外,陣列內容本身通常也是有用的,表示為達到任何特定資料列所採取的「path」。

在一般情況下,需要檢查多個欄位來識別是一個循環,請使用資料列陣列來處理。例如,如果我們需要比較欄位 f1 和 f2:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
        SELECT g.id, g.link, g.data, 1,
          ARRAY[ROW(g.f1, g.f2)],
          false
        FROM graph g
      UNION ALL
        SELECT g.id, g.link, g.data, sg.depth + 1,
          path || ROW(g.f1, g.f2),
          ROW(g.f1, g.f2) = ANY(path)
        FROM graph g, search_graph sg
        WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

小技巧

在通常情況下,只需要檢查一個欄位來識別週期,就會省略 ROW() 語法。這可以使用簡單的陣列而不用複合型別的陣列,從而獲得效率。

小技巧

遞迴查詢評估演算法以廣度優先搜尋次序產生其輸出。你也可以按照深度優先的搜尋順序顯示結果,方法是以外部查詢 ORDER BY 「path」欄位。

當你不確定是否可能會形成循環時,測試查詢的一個有用的技巧是在父查詢中放置一個 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 與 SELECT 一起使用,但是它也可以以同樣的方式附加到INSERT、UPDATE 或 DELETE。在每種情況下,它都有效地提供了可以在主查詢中可引用的臨時資料表。

7.8.2. WITH 中的資料修改語法

你可以在 WITH 中使用資料修改語法(INSERT、UPDATE 或 DELETE)。 這使你可以在同一個查詢中執行多個不同的操作。範例如下:

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 中的子查詢。這是必要的,因為資料修改語句只能在最上層語句的 WITH 子句中使用。但是,以一般的 WITH 變數可見性規則,可以從子查詢中引用 WITH 語句的輸出。

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 中的資料修改語句只執行一次,並且一定會完成,與主查詢是否讀取其所有(或者甚至是任何)輸出無關。請注意,這與 SELECT 在 WITH 之中的規則不同:如前一節所述,僅在主要查詢要求其輸出的情況下執行 SELECT。

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 規則。

Previous7.7. 列舉資料Next8. 資料型別

Last updated 6 years ago

Was this helpful?

WITH 中的資料修改語句通常具有 RETURNING 子句(詳見),如上例所示。它是 RETURNING 子句的輸出,而不是資料修改語句的目標資料表,它構成了查詢的其餘部分可以引用的臨時資料表。如果 WITH 中的資料修改語句沒有 RETURNING 子句,則它就不構成臨時資料表,並且不能在查詢的其餘部分引用。這樣的陳述將被執行。一個不是特別有用的例子是:

WITH 中的子語句會與主查詢平行執行。因此,在 WITH 中使用資料修改語句時,指定更新的實際發生順序是不可預知的。所有的語句都使用相同的快照執行(見),所以它們不能在目標資料表上「看到」對方所產生的變更。 這減輕了資料列更新的實際順序時不可預測性的影響,並且意味著回傳資料是在不同 WITH 子語句和主查詢之間傳遞變化的唯一方式。 範例如下所示:

第 6.4 節
第 13 章