PostgreSQL 正體中文使用手冊
PostgreSQL.TW官方使用手冊小島故事加入社團
14
14
  • 簡介
  • 前言
    • 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. Generated Columns
      • 5.4. 限制條件
      • 5.5. 系統欄位
      • 5.6. 表格變更
      • 5.7. 權限
      • 5.8. 資料列安全原則
      • 5.9. Schemas
      • 5.10. 繼承
      • 5.11. 分割資料表
      • 5.12. 外部資料
      • 5.13. 其他資料庫物件
      • 5.14. 相依性追蹤
    • 6. 資料處理
      • 6.1. 新增資料
      • 6.2. 更新資料
      • 6.3. 刪除資料
      • 6.4. 修改並回傳資料
    • 7. 資料查詢
      • 7.1. 概觀
      • 7.2. 資料表表示式
      • 7.3. 取得資料列表
      • 7.4. 合併查詢結果
      • 7.5. 資料排序
      • 7.6. LIMIT 和 OFFSET
      • 7.7. VALUES 列舉資料
      • 7.8. WITH Querys(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. Domain Types
      • 8.19. 物件指標型別
      • 8.20. pg_lsn 型別
      • 8.21. 概念型別
    • 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. UUID Functions
      • 9.15. XML 函式
      • 9.16. JSON 函式及運算子
      • 9.17. 序列函式
      • 9.18. 條件表示式
      • 9.19. 陣列函式及運算子
      • 9.20. 範圍函式及運算子
      • 9.21. 彙總函數
      • 9.22. Window 函式
      • 9.23. 子查詢
      • 9.24. 資料列與陣列的比較運算
      • 9.25. 集合回傳函式
      • 9.26. 系統資訊函數
      • 9.27. 系統管理函式
      • 9.28. 觸發函式
      • 9.29. 事件觸發函式
      • 9.30. Statistics Information Functions
    • 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. Index-Only Scans and Covering Indexes
      • 11.10. 運算子物件及家族
      • 11.11. 索引與排序規則
      • 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. Installation from Binaries
    • 17. 用原始碼安裝
      • 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. 平台相關的注意事項
    • 18. 用原始碼在 Windows 上安裝
      • 17.1. Building with Visual C++ or the Microsoft Windows SDK
    • 19. 服務配置與維運
      • 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 GSSAPI Encryption
      • 18.11. Secure TCP/IP Connections with SSH Tunnels
      • 18.12. 在 Windows 註冊事件日誌
    • 20. 服務組態設定
      • 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. 執行階段統計資訊
      • 19.10. 自動資料庫清理
      • 20.11. 用戶端連線預設參數
      • 19.12. 交易鎖定管理
      • 19.13. 版本與平台的相容性
      • 19.14. Error Handling
      • 19.15. 預先配置的參數
      • 19.16. Customized Options
      • 19.17. Developer Options
      • 19.18. Short Options
    • 21. 使用者認證
      • 20.1. 設定檔:pg_hba.conf
      • 20.2. User Name Maps
      • 20.3. Authentication Methods
      • 20.4. Trust Authentication
      • 20.5. Password Authentication
      • 20.6. GSSAPI Authentication
      • 20.7. SSPI Authentication
      • 20.8. Ident Authentication
      • 20.9. Peer Authentication
      • 20.10. LDAP Authentication
      • 20.11. RADIUS Authentication
      • 20.12. Certificate Authentication
      • 20.13. PAM Authentication
    • 22. 資料庫角色
      • 22.1. Database Roles
      • 22.2. Role Attributes
      • 22.3. Role Membership
      • 22.4. 移除角色
      • 22.5. Default Roles
      • 22.6. Function Security
    • 23. Managing Databases
      • 22.1. Overview
      • 22.2. Creating a Database
      • 22.3. 樣版資料庫
      • 22.4. Database Configuration
      • 22.5. Destroying a Database
      • 22.6. Tablespaces
    • 24. 語系
      • 23.1. 語系支援
      • 23.2. Collation Support
      • 23.3. 字元集支援
    • 25. 例行性資料庫維護工作
      • 25.1. 例行性資料清理
      • 25.2. 定期重建索引
      • 25.3. Log 檔案維護
    • 26. 備份及還原
      • 25.1. SQL Dump
      • 25.2. 檔案系統層級備份
      • 25.3. Continuous Archiving and Point-in-Time Recovery (PITR)
    • 27. High Availability, Load Balancing, and Replication
      • 26.1. 比較不同的解決方案
      • 26.2. 日誌轉送備用伺服器 Log-Shipping Standby Servers
      • 26.3. Failover
      • 26.4. Alternative Method for Log Shipping
      • 26.5. Hot Standby
    • 28. 監控資料庫活動
      • 27.1. Standard Unix Tools
      • 27.2. 統計資訊收集器
      • 27.3. Viewing Locks
      • 27.4. Progress Reporting
      • 27.5. Dynamic Tracing
    • 29. 監控磁碟使用情況
      • 28.1. 瞭解磁碟使用情形
      • 28.2. 磁碟空間不足錯誤
    • 30. 高可靠度及預寫日誌
      • 29.1. 可靠度
      • 29.2. Write-Ahead Logging(WAL)
      • 29.3. Asynchronous Commit
      • 29.4. WAL Configuration
      • 29.5. WAL Internals
    • 31. 邏輯複寫(Logical Replication)
      • 30.1. 發佈(Publication)
      • 30.2. 訂閱(Subscription)
      • 30.3. 衝突處理
      • 30.4. 限制
      • 30.5. 架構
      • 30.6. 監控
      • 30.7. 安全性
      • 30.8. 系統設定
      • 30.9. 快速設定
    • 32. Just-in-Time Compilation(JIT)
      • 31.1. What is JIT compilation?
      • 31.2. When to JIT?
      • 31.3. Configuration
      • 31.4. Extensibility
    • 33. 迴歸測試
      • 32.1. Running the Tests
      • 32.2. Test Evaluation
      • 32.3. Variant Comparison Files
      • 32.4. TAP Tests
      • 32.5. Test Coverage Examination
  • IV. 用戶端介面
    • 33. libpq - C Library
      • 33.1. 資料庫連線控制函數
      • 33.2. 連線狀態函數
      • 33.3. Command Execution Functions
      • 33.4. Asynchronous Command Processing
      • 33.5. Retrieving Query Results Row-By-Row
      • 33.6. Canceling Queries in Progress
      • 33.7. The Fast-Path Interface
      • 33.8. Asynchronous Notification
      • 33.9. Functions Associated with the COPY Command
      • 33.10. Control Functions
      • 33.11. Miscellaneous Functions
      • 33.12. Notice Processing
      • 33.13. Event System
      • 33.14. 環境變數
      • 33.15. 密碼檔
      • 33.16. The Connection Service File
      • 33.17. LDAP Lookup of Connection Parameters
      • 33.18. SSL Support
      • 33.19. Behavior in Threaded Programs
      • 33.20. Building libpq Programs
      • 33.21. Example Programs
    • 34. Large Objects
      • 35.1. Introduction
      • 35.2. Implementation Features
      • 35.3. Client Interfaces
      • 35.4. Server-side Functions
      • 35.5. Example Program
    • 35. ECPG - Embedded SQL in C
      • 35.1. The Concept
      • 35.2. Managing Database Connections
      • 35.3. Running SQL Commands
      • 35.4. Using Host Variables
      • 35.5. Dynamic SQL
      • 35.6. pgtypes Library
      • 35.7. Using Descriptor Areas
      • 35.8. Error Handling
      • 35.9. Preprocessor Directives
      • 35.10. Processing Embedded SQL Programs
      • 35.11. Library Functions
      • 35.12. Large Objects
      • 35.13. C++ Applications
      • 35.14. Embedded SQL Commands
      • 35.15. Informix Compatibility Mode
      • 35.16. Internals
    • 36. The Information Schema
      • 36.1. The Schema
      • 36.2. Data Types
      • 36.3. information_schema_catalog_name
      • 36.4. administrable_role_authorizations
      • 36.5. applicable_roles
      • 36.6. attributes
      • 36.7. character_sets
      • 36.8. check_constraint_routine_usage
      • 36.9. check_constraints
      • 36.10. collations
      • 36.11. collation_character_set_applicability
      • 36.12. column_domain_usage
      • 36.13. column_options
      • 36.14. column_privileges
      • 36.16. column_udt_usage
      • 36.17. columns
      • 36.18. 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
      • 36.32. key_column_usage
      • 36.33. parameters
      • 36.34. referential_constraints
      • 37.34. role_column_grants
      • 37.35. role_routine_grants
      • 36.37. role_table_grants
      • 37.37. role_udt_grants
      • 37.38. role_usage_grants
      • 37.39. routine_privileges
      • 37.40. routines
      • 36.42. 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
      • 36.51. table_constraints
      • 36.49. 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
      • 37.2. The PostgreSQL Type System
      • 37.3. 使用者自訂函數
      • 37.4. User-defined Procedures
      • 37.5. Query Language (SQL) Functions
      • 37.6. Function Overloading
      • 37.7. 函數易變性類別
      • 37.8. Procedural Language Functions
      • 37.9. Internal Functions
      • 37.10. C-Language Functions
      • 37.11. Function Optimization Information
      • 37.12. User-defined Aggregates
      • 37.13. User-defined Types
      • 37.14. User-defined Operators
      • 37.15. Operator Optimization Information
      • 38.16. Interfacing Extensions To Indexes
      • 37.17. 封裝相關物件到延伸功能中
      • 37.18. Extension Building Infrastructure
    • 38. Triggers
      • 38.1. Overview of Trigger Behavior
      • 38.2. Visibility of Data Changes
      • 38.3. Writing Trigger Functions in C
      • 38.4. A Complete Trigger Example
    • 39. Event Triggers (事件觸發)
      • 39.1. Overview of Event Trigger Behavior
      • 39.2. Event Trigger Firing Matrix
      • 39.3. Writing Event Trigger Functions in C
      • 39.4. A Complete Event Trigger Example
    • 40. 規則系統
      • 40.1. The Query Tree
      • 40.2. Views and the Rule System
      • 40.3. Materialized Views
      • 40.4. Rules on INSERT, UPDATE, and DELETE
      • 40.5. 規則及權限
      • 40.6. Rules and Command Status
      • 40.7. Rules Versus Triggers
    • 41. Procedural Languages(程序語言)
      • 41.1. Installing Procedural Languages
      • 41.2. Structure of PL/pgSQL
      • 41.5. Basic Statements
      • 41.11. 深入了解 PL/pgSQL
    • 42. PL/pgSQL - SQL Procedural Language
      • 42.1. Overview
      • 42.2. Structure of PL/pgSQL
      • 42.3. Declarations
      • 42.4. Expressions
      • 42.5. 基本語法
      • 42.6. Control Structures
    • 43. PL/Tcl - Tcl Procedural Language
    • 44. PL/Perl — Perl Procedural Language
    • 45. PL/Python - Python Procedural Language
      • 45.1. Python 2 vs. Python 3
      • 45.2. PL/Python Functions
      • 45.3. Data Values
      • 45.4. Sharing Data
      • 45.5. Anonymous Code Blocks
      • 45.6. Trigger Functions
      • 45.7. Database Access
      • 45.8. Explicit Subtransactions
      • 45.9. Transaction Management
      • 45.10. Utility Functions
      • 45.11. Environment Variables
    • 46. Server Programming Interface
    • 47. Background Worker Processes
    • 48. Logical Decoding
      • 48.1. Logical Decoding Examples
      • 48.2. Logical Decoding Concepts
      • 48.3. Streaming Replication Protocol Interface
      • 48.4. Logical Decoding SQL Interface
      • 48.5. System Catalogs Related to Logical Decoding
      • 48.6. Logical Decoding Output Plugins
      • 48.7. Logical Decoding Output Writers
      • 48.8. Synchronous Replication Support for Logical Decoding
    • 49. 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 SYSTEM
      • ALTER TABLE
      • ALTER TABLESPACE
      • ALTER TRIGGER
      • ALTER TYPE
      • ALTER USER
      • ALTER VIEW
      • ANALYZE
      • CLUSTER
      • COMMENT
      • COMMIT PREPARED
      • COPY
      • CREATE ACCESS METHOD
      • CREATE CAST
      • CREATE DATABASE
      • CREATE EVENT TRIGGER
      • 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
      • DEALLOCATE
      • DELETE
      • DO
      • DROP ACCESS METHOD
      • DROP DATABASE
      • DROP EXTENSION
      • DROP FUNCTION
      • DROP INDEX
      • DROP LANGUAGE
      • DROP MATERIALIZED VIEW
      • DROP OWNED
      • DROP POLICY
      • DROP PUBLICATION
      • 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
      • PREPARE TRANSACTION
      • REASSIGN OWNED
      • REFRESH MATERIALIZED VIEW
      • REINDEX
      • RESET
      • REVOKE
      • ROLLBACK PREPARED
      • SECURITY LABEL
      • 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
      • oid2name
      • pgbench
      • pg_basebackup
      • pg_dump
      • pg_dumpall
      • pg_isready
      • pg_receivewal
      • pg_recvlogical
      • pg_restore
      • pg_verifybackup
      • psql
      • vacuumdb
    • III. PostgreSQL 伺服器應用程式
      • initdb
      • pg_archivecleanup
      • pg_ctl
      • pg_standby
      • pg_test_timing
      • pg_upgrade
      • postgres
  • VII. 資料庫進階
    • 50. PostgreSQL 的內部架構
      • 50.1. 處理查詢語句的流程
      • 50.2. How Connections Are Established
      • 50.3. The Parser Stage
      • 50.4. The PostgreSQL Rule System
      • 50.5. Planner/Optimizer
      • 50.6. Executor
    • 51. 系統目錄
      • 51.3. pg_am
      • 51.7. pg_attribute
      • 51.8. pg_authid
      • 51.9. pg_auth_members
      • 51.10. pg_cast
      • 51.11 pg_class
      • 51.12. pg_collation
      • 51.13. pg_constraint
      • 51.15 pg_database
      • 51.21. pg_event_trigger
      • 51.22. pg_extension
      • 51.26 pg_index
      • 51.29. pg_language
      • 51.32. pg_namespace
      • 51.33. pg_opclass
      • 51.38. pg_policy
      • 51.39. pg_proc
      • 51.44. pg_rewrite
      • 51.49. pg_statistic
      • 51.50. pg_statistic_ext
      • 51.52. pg_subscription
      • 51.53. pg_subscription_rel
      • 51.54. pg_tablespace
      • 51.56. pg_trigger
      • 51.62. pg_type
      • 51.66. pg_available_extensions
      • 51.67. pg_available_extension_versions
      • 51.71. pg_hba_file_rules
      • 51.72. pg_indexes
      • 51.73. pg_locks
      • 51.77. pg_prepared_xacts
      • 51.79. pg_replication_origin_status
      • 51.80. pg_replication_slots
      • 51.82 pg_roles
      • 51.85. pg_settings
      • 51.87. pg_shmem_allocations
      • 51.88. pg_stats
      • 51.90. pg_tables
      • 51.93. pg_user
      • 51.95. pg_views
    • 52. Frontend/Backend Protocol
      • 52.1. Overview
      • 52.2. Message Flow
      • 52.3. SASL Authentication
      • 52.4. Streaming Replication Protocol
      • 52.5. Logical Streaming Replication Protocol
      • 52.6. Message Data Types
      • 52.7. Message Formats
      • 52.8. Error and Notice Message Fields
      • 52.9. Logical Replication Message Formats
      • 52.10. Summary of Changes since Protocol 2.0
    • 53. PostgreSQL 程式撰寫慣例
      • 53.1. Formatting
      • 53.2. Reporting Errors Within the Server
      • 53.3. Error Message Style Guide
      • 53.4. Miscellaneous Coding Conventions
    • 54. Native Language Support
      • 54.1. For the Translator
      • 54.2. For the Programmer
    • 55. 撰寫程序語言的處理程序
    • 56. Writing a Foreign Data Wrapper
      • 56.1. Foreign Data Wrapper Functions
      • 56.2. Foreign Data Wrapper Callback Routines
      • 56.3. Foreign Data Wrapper Helper Functions
      • 56.4. Foreign Data Wrapper Query Planning
      • 56.5. Row Locking in Foreign Data Wrappers
    • 59. Genetic Query Optimizer
      • 59.1. Query Handling as a Complex Optimization Problem
      • 59.2. Genetic Algorithms
      • 59.3. Genetic Query Optimization (GEQO) in PostgreSQL
      • 59.4. Further Reading
    • 60. Table Access Method Interface Definition
    • 61. Index Access Method Interface Definition
    • 62. Generic WAL Records
    • 64. B-Tree Indexes
      • 64.1. Introduction
      • 64.2. Behavior of B-Tree Operator Classes
      • 64.3. B-Tree Support Functions
      • 64.4. Implementation
    • 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. 延伸介面
      • 66.4. 實作說明
      • 66.5. GIN 小巧技
      • 66.6. 限制
      • 66.7. 範例
    • 67. BRIN Indexes
      • 67.1. Introduction
      • 67.2. Built-in Operator Classes
      • 67.3. Extensibility
    • 68. 資料庫實體儲存格式
      • 68.1. Database File Layout
      • 68.2. TOAST
      • 68.3. Free Space Map
      • 68.4 可視性映射表(Visibility Map)
      • 68.5. The Initialization Fork
      • 68.6. Database Page Layout
    • 69. System Catalog Declarations and Initial Contents
    • 70. 查詢計畫如何使用統計資訊
      • 70.1. Row Estimation Examples
      • 70.2. 多元統計資訊範例
      • 70.3. Planner Statistics and Security
    • 71. Backup Manifest Format
  • VIII. 附錄
    • A. PostgreSQL 錯誤代碼
    • B. 日期時間格式支援
      • B.1. 日期時間解譯流程
      • B.2. 日期時間慣用字
      • B.3. 日期時間設定檔
      • B.4. 日期時間的沿革
    • C. SQL 關鍵字
    • D. SQL 相容性
      • D.1. Supported Features
      • D.2. Unsupported Features
      • D.3. XML Limits and Conformance to SQL/XML
    • E. 版本資訊
      • E.1. Release 14
    • F. 延伸支援模組
      • F.1. adminpack
      • F.2. amcheck
      • F.3. auth_delay
      • F.4. auto_explain
      • F.5. bloom
      • F.6. btree_gin
      • F.10. dblink
        • dblink_connect
        • dblink_connect_u
        • dblink_disconnect
        • dblink
        • dblink_exec
        • dblink_open
        • dblink_fetch
        • dblink_close
        • dblink_get_connections
        • dblink_error_message
        • dblink_send_query
        • dblink_is_busy
        • dblink_get_notify
        • dblink_get_result
        • dblink_cancel_query
        • dblink_get_pkey
        • dblink_build_sql_insert
        • dblink_build_sql_delete
        • dblink_build_sql_update
      • F.13. earthdistance
      • F.14. file_fdw
      • F.16. hstore
      • F.24. pg_buffercache
      • F.29. pg_stat_statements
      • F.30. pgstattuple
      • F.31. pg_trgm
      • F.32. pg_visibility
      • F.33. postgres_fdw
      • F.35. sepgsql
      • F.38. tablefunc
      • F.40. test_decoding
      • F.41. tsm_system_rows
      • F.42. tsm_system_time
      • F.44. uuid-ossp
    • 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. PostgreSQL Limits
    • L. 縮寫字
    • M. Glossary
    • N. 色彩支援
      • N.1. When Color is Used
      • N.2. Configuring the Colors
  • 參考書目
Powered by GitBook
On this page

Was this helpful?

Edit on GitHub
Export as PDF
  1. VII. 資料庫進階

55. 撰寫程序語言的處理程序

Previous54.2. For the ProgrammerNext56. Writing a Foreign Data Wrapper

Last updated 3 years ago

Was this helpful?

使用目前編譯語言的「version 1」介面以外的語言撰寫的函數(包括使用者定義的程序語言函數和用 SQL 撰寫的函數)在被呼叫時,都將透過特定語言的呼叫處理程序函數。呼叫處理程序有責任以有意義的方式執行功能,例如透過解譯程式原始碼。本章概述如何撰寫新程序語言的呼叫處理程序。

程序語言的呼叫處理程序是「一般」函數,必須使用「version 1」介面以編譯語言(例如 C)撰寫,並在 PostgreSQL 中註冊為不帶任何參數且回傳 language_handler 型別。這種特殊的偽型別將函數標識為呼叫處理程序,並防止在 SQL 指令中直接呼叫該函數。有關 C 語言呼叫約定和動態載入的更多詳細訊,請參閱。

呼叫處理程序的呼叫方式與其他任何函數相同:它會接收到一個指向 FunctionCallInfoBaseData 結構的指標,該結構包含參數值和有關被呼叫函數的資訊,並且期待回傳 Datum 結果(也可能設定 FunctionCallInfoBaseData 結構中 isnull 的欄位,如果該函數希望回傳 SQL NULL 的結果)。呼叫處理程序和普通被呼叫函數之間的區別在於 FunctionCallInfoBaseData 結構的 flinfo->fn_oid 欄位會包含要呼叫實際函數的 OID,而不包含呼叫處理程序本身的 OID。呼叫處理程序必須使用此欄位來決定所要執行的功能。同樣地,傳遞的參數列表是根據目標函數而不是呼叫處理程序的宣告設定。

呼叫處理程序要從 pg_proc 系統目錄中取得函數的項目,並分析被呼叫函數的參數和回傳型別。該函數的 CREATE FUNCTION 命令的 AS 子句可以在 pg_proc 行的 prosrc 欄位中看到。這通常是程序語言的原始碼,但是從理論上說,它也可能是其他內容,例如檔案的路徑名稱,或告訴呼叫處理程序詳細操作的其他任何內容。

通常,每個 SQL 語句會多次呼叫同一函數。 呼叫處理程序可以透過使用 flinfo->fn_extra 欄位來避免重複查詢有關被呼叫函數的資訊。最初將為 NULL,但可以由呼叫處理程序設定為指向有關被呼叫函數的資訊。在後續的呼叫中,如果 flinfo->fn_extra 已經為非 NULL,則可以使用它,並且跳過資訊查詢步驟。呼叫處理程序必須確保使 flinfo->fn_extra 指向至少可以保存到目前查詢結束的快取,因為 FmgrInfo 資料結構可以保留那麼長時間。一種方法是在 flinfo->fn_mcxt 指定的快取內容中分配額外的資料。此類資料通常與 FmgrInfo 本身俱有相同的壽命。但是處理程序還可以選擇使用壽命更長的快取,以便它可以跨查詢快取函數定義資訊。

當將程序語言函數作為事件觸發器呼叫時,不會以通常的方式傳遞任何參數,但是 FunctionCallInfoBaseData 的 context 欄位指向 TriggerData 結構,而不是像在普通函數呼叫中那樣為 NULL。語言處理程序應提供程序語言函數的機制,以取得所觸發資訊。

This is a template for a procedural-language handler written in C:

#include "postgres.h"
#include "executor/spi.h"
#include "commands/trigger.h"
#include "fmgr.h"
#include "access/heapam.h"
#include "utils/syscache.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(plsample_call_handler);

Datum
plsample_call_handler(PG_FUNCTION_ARGS)
{
    Datum          retval;

    if (CALLED_AS_TRIGGER(fcinfo))
    {
        /*
         * Called as a trigger function
         */
        TriggerData    *trigdata = (TriggerData *) fcinfo->context;

        retval = ...
    }
    else
    {
        /*
         * Called as a function
         */

        retval = ...
    }

    return retval;
}

Only a few thousand lines of code have to be added instead of the dots to complete the call handler.

CREATE FUNCTION plsample_call_handler() RETURNS language_handler
    AS 'filename'
    LANGUAGE C;
CREATE LANGUAGE plsample
    HANDLER plsample_call_handler;

If a validator is provided by a procedural language, it must be declared as a function taking a single parameter of type oid. The validator's result is ignored, so it is customarily declared to return void. The validator will be called at the end of a CREATE FUNCTION command that has created or updated a function written in the procedural language. The passed-in OID is the OID of the function's pg_proc row. The validator must fetch this row in the usual way, and do whatever checking is appropriate. First, call CheckFunctionValidatorAccess() to diagnose explicit calls to the validator that the user could not achieve through CREATE FUNCTION. Typical checks then include verifying that the function's argument and result types are supported by the language, and that the function's body is syntactically correct in the language. If the validator finds the function to be okay, it should just return. If it finds an error, it should report that via the normal ereport() error reporting mechanism. Throwing an error will force a transaction rollback and thus prevent the incorrect function definition from being committed.

If an inline handler is provided by a procedural language, it must be declared as a function taking a single parameter of type internal. The inline handler's result is ignored, so it is customarily declared to return void. The inline handler will be called when a DO statement is executed specifying the procedural language. The parameter actually passed is a pointer to an InlineCodeBlock struct, which contains information about the DO statement's parameters, in particular the text of the anonymous code block to be executed. The inline handler should execute this code and return.

After having compiled the handler function into a loadable module (see ), the following commands then register the sample procedural language:

Although providing a call handler is sufficient to create a minimal procedural language, there are two other functions that can optionally be provided to make the language more convenient to use. These are a validator and an inline handler. A validator can be provided to allow language-specific checking to be done during . An inline handler can be provided to allow the language to support anonymous code blocks executed via the command.

Validator functions should typically honor the parameter: if it is turned off then any expensive or context-sensitive checking should be skipped. If the language provides for code execution at compilation time, the validator must suppress checks that would induce such execution. In particular, this parameter is turned off by pg_dump so that it can load procedural language functions without worrying about side effects or dependencies of the function bodies on other database objects. (Because of this requirement, the call handler should avoid assuming that the validator has fully checked the function. The point of having a validator is not to let the call handler omit checks, but to notify the user immediately if there are obvious errors in a CREATE FUNCTION command.) While the choice of exactly what to check is mostly left to the discretion of the validator function, note that the core CREATE FUNCTION code only executes SET clauses attached to a function when check_function_bodies is on. Therefore, checks whose results might be affected by GUC parameters definitely should be skipped when check_function_bodies is off, to avoid false failures when reloading a dump.

It's recommended that you wrap all these function declarations, as well as the CREATE LANGUAGE command itself, into an extension so that a simple CREATE EXTENSION command is sufficient to install the language. See for information about writing extensions.

The procedural languages included in the standard distribution are good references when trying to write your own language handler. Look into the src/pl subdirectory of the source tree. The reference page also has some useful details.

第 37.10 節
Section 37.10.5
CREATE FUNCTION
DO
check_function_bodies
Section 37.17
CREATE LANGUAGE