| 第三十八章. 擴展的 SQL: 函數 內容 查詢語言 （SQL）函數 過程語言函數 內部函數 編譯 （C）語言函數 函數重載 正如我們想象的那樣，定義新類型的一部分工作是定義描述（該類型）特征的函數．因此，我們可能只定義一個新函數而不定義新類型，反過來卻不行．所以，我們先描述如何給 Postgres 增加新函數，然后再描述如何增加新類型． PostgresSQL 提供三種類型的函數： 查詢語言函數（用 SQL 寫的函數） 過程語言函數（用諸如 PLTCL 或 PLSQL 這樣的語言寫的函數） 編程語言函數（用類似 C 這樣編譯的編程語言寫的函數） 每種函數都可以以一個基本類型或一個復合類型或是兩者的某種組合作為參數．另外，每種函數都可以返回一個基本類型或一個復合類型值．定義 SQL 函數更容易些，所以我們將從這里開始．本章的例子還可以在 funcs.sql 和 funcs.c里找到． 查詢語言（SQL）函數 SQL 函數執行一個任意 SQL 查詢的列表，返回列表里最后一個查詢的結果。SQL 函數通常返回集。如果它們的返回類型沒有聲明為 setof，那么將返回最后一條查詢結果的任意元素。 跟在 AS 后面的 SQL 函數體應該是一個用空白字符分隔和用引號括起來的查詢列表。要注意在查詢里面的引號必須用前面帶兩個反斜杠的方法轉意。 SQL 函數的參數在查詢里可以用 $n 語法引用：$1 指第一個參數，$2 指第二個參數，以此類推。如果參數是復合類型，那么可以用點表示法（例如。"$1.emp"）訪問參數里的字段或者激活函數。 例子 看看下面這個簡單的 SQL 函數的例子，它將用于對一個銀行帳號做扣款（借記消費 debit）動作： create function TP1 (int4, float8) returns int4 as 'update BANK set balance = BANK.balance - $2 where BANK.acctountno = $1 select(x = 1)' language 'sql'; 一個用戶可以象下面這樣用這個函數給帳戶 17 扣款 $100.00： select (x = TP1( 17,100.0)); 下面的更有意思的例子接受一個類型為 EMP 的參數，并且檢索多個結果： select function hobbies (EMP) returns set of HOBBIES（譯注：開頭的 select 應為 create） as 'select (HOBBIES.all) from HOBBIES where $1.name = HOBBIES.person' language 'sql'; 基本類型的 SQL 函數 最簡單的 SQL 函數可能是不帶參數，只是返回一個基本類型如 int4 的函數： CREATE FUNCTION one() RETURNS int4 AS 'SELECT 1 as RESULT' LANGUAGE 'sql'; SELECT one() AS answer; +-------+ |answer | +-------+ |1 | +-------+ 注意我們給函數定義了目標列（名稱為 RESULT），但是激活函數的查詢語句的目標列覆蓋了函數的目標列．因此，結果的標記是 answer 而不是 one 的． 定義以基本類型為參數的 SQL 函數幾乎一樣簡單，注意我們在函數內如何用$1和$2使用參數： CREATE FUNCTION add_em(int4, int4) RETURNS int4 AS 'SELECT $1 + $2;' LANGUAGE 'sql'; SELECT add_em(1, 2) AS answer; +-------+ |answer | +-------+ |3 | +-------+ 復合類型的 SQL 函數 當我們聲明的函數用復合類型（如 EMP）做參數時，我們不僅要聲明我們需要哪個參數（像上面我們使用$1和$2一樣），而且要聲明參數的字段．比如，把 double_salary 做為計算你薪水翻番之后的數值的函數： CREATE FUNCTION double_salary(EMP) RETURNS int4 AS 'SELECT $1.salary * 2 AS salary;' LANGUAGE 'sql'; SELECT name, double_salary(EMP) AS dream FROM EMP WHERE EMP.cubicle ~= '(2,1)'::point; +-----+-------+ |name | dream | +-----+-------+ |Sam | 2400 | +-----+-------+ 注意這里 $1.salary 語法的使用．在開始說明返回復合類型的函數之前，我們必須先介紹用于映射字段的函數表示法．我們可以用一個簡單的方法解釋這些：我們可以互換地使用 attribute(class) 和 class.attribute 兩種表達方式： -- -- 這是與下面語句一樣的： -- SELECT EMP.name AS youngster FROM EMP WHERE EMP.age < 30 -- SELECT name(EMP) AS youngster FROM EMP WHERE age(EMP) < 30; +----------+ |youngster | +----------+ |Sam | +----------+ 不過，我們呆會兒就會看到不總是這種情況．這種函數表示法在我們希望使用的函數返回單條記錄時是很重要的．此時我們在函數里面一個字段一個字段地組裝整條記錄．下面是返回單條 EMP 記錄的函數例子： CREATE FUNCTION new_emp() RETURNS EMP AS 'SELECT /'None/'::text AS name, 1000 AS salary, 25 AS age, /'(2,2)/'::point AS cubicle' LANGUAGE 'sql'; 在這個例子中我們把每個字段賦予了一個常量，當然我們可以用任何計算或表達式來代替這些常量．定義這樣的函數可能需要一點點技巧．下面是一些比較重要的注意事項： 目標列表的順序必須和你用 CREATE TABLE （或者你執行的 .* 查詢里的）語句創建時的字段順序一樣． 你必須很仔細的進行類型轉換 （使用 ::）．否則你將看到下面的錯誤信息： WARN::function declared to return type EMP does not retrieve (EMP.*) 當我們調用了一個返回整條記錄的函數時，我們不能檢索整條記錄．我們要么是從該記錄中映射出一個字段，要么是把整條記錄傳遞給另外一個函數． SELECT name(new_emp()) AS nobody; +-------+ |nobody | +-------+ |None | +-------+ 通常我們必須把函數的返回值映射到字段里，使用這種函數語法是因為分析器在分析函數調用時，無法正確分析與函數相聯的'點'的語法． SELECT new_emp().name AS nobody; WARN:parser: syntax error at or near "." 在 SQL 查詢語言里的任何命令的集合都可以打包在一起，定義為函數．這些命令可以包含更新動作（例如，INSERT，UPDATE，和 DELETE），就象可以使用 SELECT 查詢一樣．不過，最后的命令必須是一條返回類型與所聲明的函數返回類型的一致的 SELECT 語句。 CREATE FUNCTION clean_EMP () RETURNS int4 AS 'DELETE FROM EMP WHERE EMP.salary <= 0; SELECT 1 AS ignore_this' LANGUAGE 'sql'; SELECT clean_EMP(); +--+ |x | +--+ |1 | +--+ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 過程語言函數 過程語言函數不是內建于 Postgres 里的。它們是通過可裝載模塊提供的。請參考相關的 PL 的文檔獲取關于語法和 AS 子句如何被 PL 句柄解釋的細節。 在標準的 Postgres 版本里有兩種可用的過程語言（PLTCL 和 PLSQL），并且可以定義其他語言。請參考 過程語言 獲取詳細信息。 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 內部函數 內部函數是那些用 C 寫的函數，它們已經通過靜態鏈接的方式鏈接進入Postgres 后端進程里面。AS 子句給出函數的 C 語言的名稱，它不必與定義給 SQL 使用的名稱相同。（出于向下兼容考慮，一個空的 AS 子句可以被接受，意味著 C 函數名與 SQL 函數名相同。）通常，所有在后端里出現的內部函數都在數據庫初始化時定義為 SQL 函數，但是用戶可以用 CREATE FUNCTION 為內部函數創建額外的別名。 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 編譯（C）語言函數 用 C 寫的函數可以編譯成可動態裝載的對象，然后用于實現用戶定義的 SQL 函數。當用戶定義的函數第一次被后端調用時，動態裝載器把函數的目標碼裝載入內存，然后把這個函數與正在運行的Postgres 可執行文件鏈接起來。CREATE FUNCTION 的 SQL 語法用兩種方法之一把 SQL 函數與 C 源函數鏈接起來。如果 SQL 函數與 C 源函數同名，使用語句的第一種形式。AS 子句里的字符串參數是包含已經編譯好的可動態裝載的對象的完整的路徑名。如果 C 函數的名稱與 SQL 函數的名稱不同，則使用第二種形式。這種形式 AS 子句接受兩個字符串參數，第一個是可動態裝載目標文件的完整路徑，第二個是動態裝載器應搜索的鏈接符號。這個鏈接符號只是 C 源代碼里的函數名。 注意：在第一次使用之后，一個動態裝載的用戶函數仍然停留在內存中，因而對該函數的更進一步的調用只是簡單的符號表查找。 聲明目標文件的參數（AS 子句里的字符串）應該是該函數目標文件的完整路徑，并用引號括起來。如在 AS 子句里使用了鏈接符號，鏈接符號也應該用單引號括起來，并且就應該是 C 源代碼里函數的名稱。在 Unix 系統里，命令 nm 會打印出一個可動態裝載的對象里的所有鏈接符號。（Postgres 不會自動編譯一個函數；該函數必須在使用 CREATE FUNCTION 命令之前編譯。參閱下文獲取額外信息。） 基本類型的 C 語言函數 下表列出了被裝載入 Postgres 的 C 函數里需要的當作參數的 C 類型。"定義在" 列給出了等效的 C 類型定義的實際的頭文件（在 .../src/backend/ 目錄里）。如果你包含了utils/builtins.h，這些文件將被自動包括。 表 38-1. 內建的 Postgres 類型等效的 C 類型 內建類型 C 類型 定義在 abstime AbsoluteTime utils/nabstime.h bool bool include/c.h box (BOX *) utils/geo-decls.h bytea (bytea *) include/postgres.h char char N/A cid CID include/postgres.h datetime (DateTime *) include/c.h or include/postgres.h int2 int2 include/postgres.h int2vector (int2vector *) include/postgres.h int4 int4 include/postgres.h float4 float32 or (float4 *) include/c.h or include/postgres.h float8 float64 or (float8 *) include/c.h or include/postgres.h lseg (LSEG *) include/geo-decls.h name (Name) include/postgres.h oid oid include/postgres.h oidvector (oidvector *) include/postgres.h path (PATH *) utils/geo-decls.h point (POINT *) utils/geo-decls.h regproc regproc or REGPROC include/postgres.h reltime RelativeTime utils/nabstime.h text (text *) include/postgres.h tid ItemPointer storage/itemptr.h timespan (TimeSpan *) include/c.h or include/postgres.h tinterval TimeInterval utils/nabstime.h uint2 uint16 include/c.h uint4 uint32 include/c.h xid (XID *) include/postgres.h Postgres 內部把基本類型當作"一片內存"看待．定義在某種類型上的用戶定義函數實際上定義了 Postgres對（該數據類型）可能的操作．也就是說，Postgres 只是從磁盤讀取和存儲該數據類型，而使用你定義的函數來輸入，處理和輸出數據．基本類型可以有下面三種內部形態（格式）之一： 傳遞數值，定長（pass by value, fixed-length） 傳遞引用，定長（pass by reference, fixed-length） 傳遞引用，變長（pass by reference, variable-length） 傳遞數值的類型的長度只能是1，2 或 4 字節．（即便你的計算機支持其他長度的傳值類型也是這樣）．Postgres 本身的傳值類型只能是整數．你要仔細定義你的類型，確保它們在任何體系平臺上都是相同尺寸（字節）．例如，long 型是一個危險的類型因為在一些機器上它是 4 字節而在另外一些機器上是 8 字節，而 int 型在大多數 Unix 機器上都是4字節的（盡管不是在多數個人微機上）．在一個 Unix 機器上的 int4 合理的實現可能是： /* 4-byte integer, passed by value */ typedef int int4; 另外，任何尺寸的定長類型都可以是傳遞引用型．例如，下面是一個 Postgres 類型的實現： /* 16-byte structure, passed by reference */ typedef struct { double x, y; } Point; 只能使用指向這些類型的指針來在 Postgres 函數里輸入和輸出．最后，所有變長類型同樣也只能通過傳遞引用的方法來傳遞．所有變長類型必須以一個4字節長的長度域開始，并且所有存儲在該類型的數據必須放在緊接著長度域的存儲空間里．長度域是結構的全長（也就是說，包括長度域本身的長度）．我們可以用下面方法定義一個 text 類型： typedef struct { int4 length; char data[1]; } text; 顯然，上面的數據域不夠存儲任何可能的字串 －－ 在 C 中定義這么個結構是不可能的．當處理變長類型時，我們必須仔細分配正確的存儲器數量并初始化長度域．例如，如果我們想在一個 text 結構里存儲 40 字節，我們可能會使用象下面的代碼片段： #include "postgres.h" ... char buffer[40]; /* our source data */ ... text *destination = (text *) palloc(VARHDRSZ + 40); destination->length = VARHDRSZ + 40; memmove(destination->data, buffer, 40); ... 既然我們已經討論了基本類型所有的可能結構，我們便可以用實際的函數舉一些例子．假設 funcs.c 象下面一樣： #include #include "postgres.h" /* By Value */ int add_one(int arg) { return(arg + 1); } /* By Reference, Fixed Length */ Point * makepoint(Point *pointx, Point *pointy ) { Point *new_point = (Point *) palloc(sizeof(Point)); new_point->x = pointx->x; new_point->y = pointy->y; return new_point; } /* By Reference, Variable Length */ text * copytext(text *t) { /* * VARSIZE is the total size of the struct in bytes. */ text *new_t = (text *) palloc(VARSIZE(t)); memset(new_t, 0, VARSIZE(t)); VARSIZE(new_t) = VARSIZE(t); /* * VARDATA is a pointer to the data region of the struct. */ memcpy((void *) VARDATA(new_t), /* destination */ (void *) VARDATA(t), /* source */ VARSIZE(t)-VARHDRSZ); /* how many bytes */ return(new_t); } text * concat_text(text *arg1, text *arg2) { int32 new_text_size = VARSIZE(arg1) + VARSIZE(arg2) - VARHDRSZ; text *new_text = (text *) palloc(new_text_size); memset((void *) new_text, 0, new_text_size); VARSIZE(new_text) = new_text_size; strncpy(VARDATA(new_text), VARDATA(arg1), VARSIZE(arg1)-VARHDRSZ); strncat(VARDATA(new_text), VARDATA(arg2), VARSIZE(arg2)-VARHDRSZ); return (new_text); } 在 OSF/1 （平臺上）我們要敲入： CREATE FUNCTION add_one(int4) RETURNS int4 AS 'PGROOT/tutorial/funcs.so' LANGUAGE 'c'; CREATE FUNCTION makepoint(point, point) RETURNS point AS 'PGROOT/tutorial/funcs.so' LANGUAGE 'c'; CREATE FUNCTION concat_text(text, text) RETURNS text AS 'PGROOT/tutorial/funcs.so' LANGUAGE 'c'; CREATE FUNCTION copytext(text) RETURNS text AS 'PGROOT/tutorial/funcs.so' LANGUAGE 'c'; On other systems, we might have to make the filename end in .sl (to indicate that it's a shared library). 復合類型的 C 語言函數 復合類型不象 C 結構那樣有固定的布局．復合類型的記錄可能包含空（null）域．另外，一個屬于繼承層次一部分的復合類型可能和同一繼承范疇的其他成員有不同的域/字段．因此，Postgres 提供一個過程接口用于從 C 里面訪問復合類型．在 Postgres 處理一個記錄集時，每條記錄都將作為一個類型 TUPLE 的不透明（opaque）的結構被傳遞給你的函數．假設我們為下面查詢寫一個函數 * SELECT name, c_overpaid(EMP, 1500) AS overpaid FROM EMP WHERE name = 'Bill' or name = 'Sam'; 在上面的查詢里，我們可以這樣定義 c_overpaid ： #include "postgres.h" #include "executor/executor.h" /* for GetAttributeByName() */ bool c_overpaid(TupleTableSlot *t, /* the current instance of EMP */ int4 limit) { bool isnull = false; int4 salary; salary = (int4) GetAttributeByName(t, "salary", &isnull); if (isnull) return (false); return(salary > limit); } GetAttributeByName 是 Postgres 系統函數，用來返回當前記錄的字段值．它有三個參數：類型為 TUPLE 的傳入函數的參數，需要的字段名稱，以及一個用以確定字段是否為空（null）的返回參數指針．GetAttributeByName 會把數據正確的對齊，這樣你就可以把返回值轉換成合適的類型．例如，如果你有一個字段的名稱就是類型名，調用 GetAttributeByName 就會看起來象： char *str; ... str = (char *) GetAttributeByName(t, "name", &isnull) 下面的查詢讓 Postgres 知道 c_overpaid 函數： * CREATE FUNCTION c_overpaid(EMP, int4) RETURNS bool AS 'PGROOT/tutorial/obj/funcs.so' LANGUAGE 'c'; 當然還有其他方法在 C 函數里構造新的記錄或修改現有記錄，這些方法都太復雜，不適合在本手冊里討論． 書寫代碼 我們現在轉到了書寫編程語言函數的更難的階段．要注意：本手冊此章的內容不會讓你成為程序員．在你嘗試用 C 書寫用于 Postgres 的函數之前，你必須對 C 有很深的了解（包括對指針的使用和 malloc 存儲器管理）．雖然可以用 C 以外的其他語言如 FORTRAN 和 Pascal 書寫用于 Postgres 的共享函數，但通常很麻煩（雖然是完全可能的），因為其他語言并不遵循和 C 一樣的調用習慣．也就是說，其他語言與 C 的傳參和返回值的方式不一樣．因此我們假設你的編程語言函數是用 C 寫的． 以基本類型為參數的 C 函數可以用直接的風格書寫。內建的 Postgres 類型的 C 等效物可以通過把文件 PGROOT/src/backend/utils/builtins.h 做為頭文件包含到 C 文件里訪問。可以向 C 文件的頭部增加 #include 這一行實現這些。 制作 C 函數的基本規則如下： 大多數用于 Postgres 的頭文件（include）應該已經安裝在PGROOT/include（參見圖 2）里．你應該總是將 -I$PGROOT/include 包括在你的 cc 命令行里．有時候，你可能發現你需要的頭文件在服務器源代碼里（比如，你需要的文件被忘了裝在 include 路徑里）．在這種情況下，你可能要增加象下面一條或幾條 -I$PGROOT/src/backend -I$PGROOT/src/backend/include -I$PGROOT/src/backend/port/ -I$PGROOT/src/backend/obj （這里 是移植的名稱，如 alpha 或 sparc）． 當分配存儲器時，用 Postgres 的函數 palloc 和 pfree 取代相應的 C 庫函數 malloc 和 free．用 palloc 分配的存儲器在事務結束時會自動釋放，避免了內存泄露． 記得用 memset 或 bzero 對你的結構清零．有些函數（像 hash （散列）訪問方法，hash （散列）聯合和排序算法）計算包含在你的結構里的裸位（row bit）．即使你初始化了你的結構的所有域，仍然有可能有幾個對齊字節（結構中的洞）含有垃圾值． 大多數的 Postgres 內部類型定義在 postgres.h，所以始終包含該文件是一個好習慣．包含了 postgres.h 將自動包含 elog.h 和 palloc.h． 把你的目標碼編譯和裝載成可以動態裝入 Postgres 的庫文件總是需要一些特殊的標記。參閱 鏈接動態鏈接庫 獲取如何在你的平臺上做這件事的詳細說明。 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 函數重載 如果函數的參數不同，可以定義為同名函數。換句話說，函數名可以重載。函數也可以與一個字段/屬性同名。這時，一個復合類型的函數和一個復合類型的字段/屬性會有沖突，這種情況下總是使用字段/屬性。 名字空間沖突 對于 Postgres v7.0，SQL CREATE FUNCTION 命令的 AS 子句把 SQL 函數名和 C 源代碼的函數名脫鉤。目前這是實現函數重載的比較好的技巧。 v7.0 以前 對于用 C 寫的函數，在 CREATE FUNCTION 里定義的 SQL 名稱必須和 C 代碼里的實際函數名稱完全一樣（因此它必須是一個合法的 C 函數名）。 這樣的限制有個小小的暗示：盡管大多數的操作系統的動態鏈接過程允許你裝載任意數目的包含沖突（同名的）的函數名的共享庫，實際上它們可能用某種有趣的方式修補這樣的裝載。例如，如果你定義了一個與內建于 Postgres 的函數同名的動態裝載的函數，DEC OSF/1 的動態裝載器會令 Postgres 調用它自己內部的函數而不是令 Postgres 調用你的函數。因此，如果你希望你的函數用于不同的體系，我們建議你不要重載 C 函數名。 我們可以用一個很聰明的技巧繞開上面提到的問題。因為重載 SQL 函數沒有問題，所以你可以定義一套不同名的 C 函數，然后定義一套同名的 SQL 函數封裝，這些 SQL 函數封裝接收合適的參數類型并且調用對應的 C 函數。 另一個解決方法是避免使用動態裝載，而是把你的函數和后端進行靜態鏈接，并且把他們定義為 INTERNAL 函數。這樣，函數必須有不同的 C 名稱，但是可以定義為相同的 SQL 名稱（當然它們的參數類型不同）。這個方法避免了 SQL 封裝函數的過荷，付出的代價是制作一個客戶化的后端可執行文件。（這個選項只有在 v6.5 和以后的版本中有，因為以前的版本要求內部函數與 SQL 函數有與 C 代碼里的一樣的名稱。） -------------------------------------------------------------------------------- |
新聞熱點
疑難解答
圖片精選
