Debian PostgreSQL函數庫使用教程

PostgreSQL函數庫是擴展數據庫功能的核心組件，允許開發者通過自定義邏輯封裝重復操作。本文以Debian系統為例，介紹內置函數庫的使用及自定義函數（PL/pgSQL、C語言）的創建與管理，覆蓋從基礎到進階的關鍵場景。

一、內置函數庫快速入門

PostgreSQL內置了豐富的函數庫，涵蓋字符串、數值、日期、聚合、條件、數組、JSON等類別，無需額外安裝即可使用。以下是常見函數示例：

1. 字符串函數

• CONCAT：連接多個字符串。
  SELECT CONCAT('Hello', ' ', 'World'); → 輸出Hello World
• SUBSTRING：提取字符串片段（語法：SUBSTRING(string FROM start [FOR length])）。
  SELECT SUBSTRING('Hello World' FROM 7 FOR 5); → 輸出World
• LOWER/UPPER：轉換字符串大小寫。
  SELECT LOWER('Hello World'); → 輸出hello world
SELECT UPPER('Hello World'); → 輸出HELLO WORLD

2. 數值函數

• ABS：返回數值絕對值。
  SELECT ABS(-10); → 輸出10
• ROUND：四舍五入（可指定小數位數）。
  SELECT ROUND(3.14159, 2); → 輸出3.14
• SQRT：計算平方根。
  SELECT SQRT(16); → 輸出4

3. 日期與時間函數

• NOW：返回當前日期和時間。
  SELECT NOW(); → 輸出2025-09-26 14:30:00+08（格式取決于時區）
• CURRENT_DATE：返回當前日期。
  SELECT CURRENT_DATE; → 輸出2025-09-26
• DATE_PART：提取日期部分（如年、月、日）。
  SELECT DATE_PART('year', NOW()); → 輸出2025

4. 聚合函數

• COUNT：統計行數。
  SELECT COUNT(*) FROM users; → 輸出用戶表的行數
• SUM：計算數值列總和。
  SELECT SUM(amount) FROM transactions; → 輸出交易表的總金額
• AVG：計算數值列平均值。
  SELECT AVG(score) FROM students; → 輸出學生表的平均分數

5. 條件函數

• CASE：根據條件返回不同值。
  SELECT CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END FROM users; → 根據年齡返回Adult或Minor
• COALESCE：返回第一個非NULL值。
  SELECT COALESCE(column1, column2, 'Default Value') FROM table; → 若column1為NULL，則返回column2，否則返回column1；若均為NULL，則返回Default Value

6. 數組函數

• ARRAY_AGG：將多行數據聚合成數組。
  SELECT ARRAY_AGG(column_name) FROM table_name; → 輸出某列的所有值組成的數組
• UNNEST：將數組展開為多行。
  SELECT UNNEST(array_column) FROM table; → 將數組列的每個元素拆分為單獨的行

7. JSON函數

• json_build_object：構建JSON對象。
  SELECT json_build_object('name', 'John', 'age', 30); → 輸出{"name": "John", "age": 30}
• json_array_agg：將多行數據聚合成JSON數組。
  SELECT json_array_agg(column_name) FROM table_name; → 輸出某列的所有值組成的JSON數組

二、自定義函數（PL/pgSQL）創建與管理

PL/pgSQL是PostgreSQL的默認過程語言，適合編寫邏輯復雜的函數（如包含循環、條件判斷的操作）。以下是在Debian上的完整流程：

1. 準備工作

確保已安裝PostgreSQL及開發工具（用于后續自定義函數）：

sudo apt update
sudo apt install postgresql postgresql-contrib build-essential

2. 連接到PostgreSQL

使用postgres用戶連接到數據庫（默認數據庫名為postgres）：

sudo -u postgres psql

3. 創建函數示例

以下是一個簡單的PL/pgSQL函數，用于計算兩個整數的和：

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

• 語法說明：
  • CREATE OR REPLACE FUNCTION：創建或替換已有函數（避免函數已存在時報錯）。
  • (a INTEGER, b INTEGER)：輸入參數及類型。
  • RETURNS INTEGER：指定返回值類型。
  • AS $$ ... $$：函數體的開始與結束（$$是分隔符，可替換為其他符號，如$func$）。
  • LANGUAGE plpgsql：指定函數語言為PL/pgSQL。

4. 調用函數

使用SELECT語句調用函數：

SELECT add_numbers(1, 2);

輸出結果為3。

5. 查看函數源代碼

通過pg_proc系統表查看函數的SQL代碼：

SELECT proname, prosrc FROM pg_proc WHERE proname = 'add_numbers';

• proname：函數名。
• prosrc：函數源代碼。

6. 修改函數

直接使用CREATE OR REPLACE FUNCTION重新創建函數（修改參數、返回值或函數體）：

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN a * b; -- 修改為計算乘積
END;
$$ LANGUAGE plpgsql;

7. 刪除函數

使用DROP FUNCTION刪除函數（需指定參數類型，避免同名函數沖突）：

DROP FUNCTION add_numbers(integer, integer);

三、自定義函數（C語言）創建與管理

C語言函數適用于對性能要求極高的場景（如復雜算法、底層系統調用）。以下是在Debian上的實現步驟：

1. 安裝依賴

安裝PostgreSQL開發庫（包含postgres.h頭文件及鏈接庫）：

sudo apt install libpq-dev

2. 編寫C代碼

創建一個簡單的C函數mydelete.c，用于模擬刪除操作（返回固定值1）：

#include "postgres.h"
#include "fmgr.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(mydelete);

Datum
mydelete(PG_FUNCTION_ARGS)
{
    int32 key = PG_GETARG_INT32(0); // 獲取輸入參數
    // 此處可添加實際刪除邏輯（如調用SPI執行SQL）
    PG_RETURN_INT32(1); // 返回操作結果
}

• 關鍵宏：
  • PG_MODULE_MAGIC：標識模塊為PostgreSQL擴展（必需）。
  • PG_FUNCTION_INFO_V1：聲明函數信息（版本兼容）。
• 參數獲取：使用PG_GETARG_*系列宏（如PG_GETARG_INT32獲取整數參數）。
• 返回值：使用PG_RETURN_*系列宏（如PG_RETURN_INT32返回整數）。

3. 編譯為共享庫

使用gcc編譯C代碼為共享庫（.so文件）：

gcc -fpic -I/usr/include/postgresql/server/ -shared -o mydelete.so mydelete.c

• -fpic：生成位置無關代碼（共享庫必需）。
• -I/usr/include/postgresql/server/：包含PostgreSQL頭文件路徑（Debian默認路徑）。
• -shared：生成共享庫。
• -o mydelete.so：輸出文件名。

4. 復制共享庫到PostgreSQL目錄

將編譯后的.so文件復制到PostgreSQL的共享庫目錄（通常為/usr/lib/postgresql/<version>/lib/）：

sudo cp mydelete.so /usr/lib/postgresql/<version>/lib/

• 替換<version>為PostgreSQL版本號（如15，可通過SELECT version();查看）。

5. 加載擴展

在PostgreSQL中創建擴展（關聯共享庫）：

CREATE EXTENSION mydelete;

6. 創建函數并使用

創建SQL函數，調用C語言函數：

CREATE FUNCTION delete_record(integer) RETURNS integer
AS 'mydelete', 'mydelete'
LANGUAGE c;

• 'mydelete'：共享庫名稱（無需后綴.so）。
• 'mydelete'：C函數名稱（需與代碼中的Datum mydelete(PG_FUNCTION_ARGS)一致）。

7. 調用C函數

使用SELECT語句調用函數：

SELECT delete_record(123);

輸出結果為1（模擬刪除操作的返回值）。

8. 卸載擴展

若不再需要擴展，可使用DROP EXTENSION卸載：

DROP EXTENSION mydelete;

四、注意事項

1. 權限管理：創建函數需具備相應權限（如CREATE FUNCTION），生產環境建議使用專用用戶。
2. 錯誤處理：PL/pgSQL可使用EXCEPTION塊捕獲異常，C語言需通過elog函數記錄錯誤。
3. 性能優化：C語言函數適合高頻或復雜操作，但開發成本高；PL/pgSQL適合業務邏輯封裝。
4. 版本兼容：自定義函數需與PostgreSQL版本匹配（如C函數接口可能隨版本變化）。

通過以上步驟，你可在Debian系統上高效使用PostgreSQL函數庫，滿足從基礎到高級的需求。