PostgreSQL資料庫擴充套件語言程式設計之plpgsql-1
標籤
PostgreSQL , plpgsql , server programming , 儲存過程 , 函式 , UDF , create language , plpython , pljava , plr , plperl , pltcl
背景
PostgreSQL是一個開放的資料庫，開發性表現在支援自定義資料型別、索引方法、索引、操作符、聚合、視窗、服務端程式語言等等。
所以我們可以看到在PostgreSQL的生態中有很多貼近業務的用法，比如在PostgreSQL中儲存和處理化學分子、儲存和處理影像、儲存和處理基因資料、儲存和處理文字（包括正則、模糊、全文檢索等技術）、訪問外部任意資料來源等。
接下來我會花一些時間給大家介紹一下PostgreSQL的服務端程式設計，這也是PostgreSQL開放性的表現之一。
通常我們的用法可能是這樣的，程式語言，通過對應的資料庫驅動，連線到資料庫，如果要實現一些資料的處理時，需要將資料下拉到客戶端，在客戶端的語言中進行處理。
而PostgreSQL還可以這樣使用，把程式語言和資料庫融為一體，你可以把程式放到資料庫裡面去執行，這樣的話資料庫幾乎可以做任何事情（只要程式能做的事情，資料庫都可以做）。
它的好處是非常明顯的，你可以把任何程式語言（只要是地球語言），作為它的服務端語言，你可以用它在資料庫中做任何事情，以前你可能總覺得資料庫就是select, update, insert, delete，現在你要改變一下你的想法了。
比如你需要處理一大批的資料，原來從資料庫拉取到程式端處理，可能把程式跑掛（比如記憶體溢位），又或者網路傳輸需要花費太多的時間。
又比如你原來的一次業務邏輯需要與資料庫互動幾十次，總體開銷可能達到了幾秒（線上業務的使用者完全忍受不了），現在，你把業務邏輯和資料結合在一起，不需要互動幾十次，一次就夠了，延遲一下就降低了。
那麼PostgreSQL是如何支援這麼多的語言的呢？
安裝擴充套件語言
https://www.postgresql.org/docs/devel/static/xplang.html
安裝擴充套件語言很簡單，通常只需要 create extension language_name 即可，目前系統自帶的語言包括python, perl, tcl, plpgsql.
編譯時帶上對應的引數
--with-tcl build Tcl modules (PL/Tcl)
--with-tclconfig=DIR tclConfig.sh is in DIR
--with-perl build Perl modules (PL/Perl)
--with-python build Python modules (PL/Python)
這樣的話，軟體安裝好後，就會帶對應的擴充套件語言，例如
$PGHOME/lib
-rwxr-xr-x 1 digoal users 63K Jan 10 14:19 pltcl.so
在需要使用該語言的資料庫中建立擴充套件語言，也可以在模板庫中建立，這樣的話以後通過模板庫建立的資料庫就預設帶有這個擴充套件語言了
postgres=# create extension pltcl;
CREATE EXTENSION
postgres=# create extension pltclu;
CREATE EXTENSION
postgres=# CREATE or REPLACE FUNCTION tcl_max(integer, integer) RETURNS integer AS $$
if {$1 > $2} {return $1}
return $2
$$ LANGUAGE pltcl STRICT;
CREATE FUNCTION
postgres=# select tcl_max(1,2);
tcl_max
---------
2
(1 row)
擴充套件語言包括如下幾個部分，其中language_handler為必須的
1. language_handler，必須為擴充套件語言建立一個對應的language_handler
CREATE FUNCTION handler_function_name()
RETURNS language_handler
AS `path-to-shared-object`
LANGUAGE C;
2. 當這個擴充套件語言需要使用do的語法（即不建立函式，直接跑程式碼）時，則需要實現一個對應的inline_function
CREATE FUNCTION inline_function_name(internal)
RETURNS void
AS `path-to-shared-object`
LANGUAGE C;
3. 如果需要讓資料庫支援這個語言的函式，在建立函式時檢查內部的程式碼，則需要實現對應的validator
PostgreSQL 引數 check_function_bodies = on 時，如果這個擴充套件語言實現了validator，那麼使用這個擴充套件語言create function時會自動檢查函式體的內容，比如語法是否正確。
CREATE FUNCTION validator_function_name(oid)
RETURNS void
AS `path-to-shared-object`
LANGUAGE C STRICT;
4. 建立擴充套件語言
CREATE [TRUSTED] [PROCEDURAL] LANGUAGE language-name
HANDLER handler_function_name
[INLINE inline_function_name]
[VALIDATOR validator_function_name] ;
例子
CREATE FUNCTION plperl_call_handler() RETURNS language_handler AS
`$libdir/plperl` LANGUAGE C;
CREATE FUNCTION plperl_inline_handler(internal) RETURNS void AS
`$libdir/plperl` LANGUAGE C;
CREATE FUNCTION plperl_validator(oid) RETURNS void AS
`$libdir/plperl` LANGUAGE C STRICT;
CREATE TRUSTED PROCEDURAL LANGUAGE plperl
HANDLER plperl_call_handler
INLINE plperl_inline_handler
VALIDATOR plperl_validator;
trusted 與 untrusted 擴充套件語言
The optional key word TRUSTED specifies that the language does not grant access to data that the user would not otherwise have.
Trusted languages are designed for ordinary database users (those without superuser privilege) and allows them to safely create functions and trigger procedures.
Since PL functions are executed inside the database server, the TRUSTED flag should only be given for languages that do not allow access to database server internals or the file system.
The languages PL/pgSQL, PL/Tcl, and PL/Perl are considered trusted;
the languages PL/TclU, PL/PerlU, and PL/PythonU are designed to provide unlimited functionality and should not be marked trusted.
trusted 指 這個語言不能訪問越權的資料（如資料庫伺服器的檔案、資料庫內部（包括直接訪問共享記憶體））
untrusted 指 這個語言沒有任何限制，允許訪問任何資料（包括檔案，網路，共享LIB庫等，危害性較大），但是功能更加強大。
編寫擴充套件語言
編寫擴充套件語言主要就是實現前面講的幾個內容，包括call handler, inline handler, validator。
範例可以參考PostgreSQL內建的幾個語言的程式碼，比如python
postgresql-9.6.1/src/pl/plpython
Datum
plpython_call_handler(PG_FUNCTION_ARGS)
{
Datum retval;
PLyExecutionContext *exec_ctx;
ErrorContextCallback plerrcontext;
PLy_initialize();
/* Note: SPI_finish() happens in plpy_exec.c, which is dubious design */
if (SPI_connect() != SPI_OK_CONNECT)
elog(ERROR, "SPI_connect failed");
/*
* Push execution context onto stack. It is important that this get
* popped again, so avoid putting anything that could throw error between
* here and the PG_TRY. (plpython_error_callback expects the stack entry
* to be there, so we have to make the context first.)
*/
exec_ctx = PLy_push_execution_context();
/*
* Setup error traceback support for ereport()
*/
plerrcontext.callback = plpython_error_callback;
plerrcontext.previous = error_context_stack;
error_context_stack = &plerrcontext;
PG_TRY();
{
Oid funcoid = fcinfo->flinfo->fn_oid;
PLyProcedure *proc;
if (CALLED_AS_TRIGGER(fcinfo))
{
Relation tgrel = ((TriggerData *) fcinfo->context)->tg_relation;
HeapTuple trv;
proc = PLy_procedure_get(funcoid, RelationGetRelid(tgrel), true);
exec_ctx->curr_proc = proc;
trv = PLy_exec_trigger(fcinfo, proc);
retval = PointerGetDatum(trv);
}
else
{
proc = PLy_procedure_get(funcoid, InvalidOid, false);
exec_ctx->curr_proc = proc;
retval = PLy_exec_function(fcinfo, proc);
}
}
PG_CATCH();
{
PLy_pop_execution_context();
PyErr_Clear();
PG_RE_THROW();
}
PG_END_TRY();
/* Pop the error context stack */
error_context_stack = plerrcontext.previous;
/* ... and then the execution context */
PLy_pop_execution_context();
return retval;
}
建立擴充套件語言與資料庫的型別轉換
https://www.postgresql.org/docs/devel/static/sql-createtransform.html
因為資料庫有自己的型別定義和表達方法，而擴充套件語言有自己的型別支援。
比如PostgreSQL支援hstore資料型別，而python沒有這個型別，那麼預設情況下python就只能使用text來表達資料庫的hstore.
PostgreSQL允許使用者自定義資料型別轉換方法，將資料庫支援的型別與擴充套件語言支援的型別橋樑建立起來。
語法如下
CREATE [ OR REPLACE ] TRANSFORM FOR type_name LANGUAGE lang_name (
FROM SQL WITH FUNCTION from_sql_function_name (argument_type [, ...]),
TO SQL WITH FUNCTION to_sql_function_name (argument_type [, ...])
);
例子
varchar_transform(internal) : 將資料庫型別轉換為SQL型別。
int4recv(internal) : 將SQL語言型別轉換為資料庫型別。
假設我們使用sql語言建立了一個資料庫函式
當函式引數型別為INT時，那麼呼叫varchar_transform，將使用者傳入的資料庫INT值 轉換為 SQL函式支援的型別，在SQL函式內部處理轉換後的值。
當函式返回型別為INT時，那麼在SQL函式內部，返回結果前，需要呼叫int4recv，將SQL函式內的return result值 轉換為 資料庫INT型別輸出。
CREATE TRANSFORM FOR int LANGUAGE sql (
FROM SQL WITH FUNCTION varchar_transform(internal),
TO SQL WITH FUNCTION int4recv(internal));
C擴充套件語言型別對映
https://www.postgresql.org/docs/9.6/static/xfunc-c.html#XFUNC-C-BASETYPE
C擴充套件語言的資料庫型別與C語言型別對映關係如下
| SQL Type | C Type | Defined In |
|---|---|---|
| abstime | AbsoluteTime | utils/nabstime.h |
| bigint (int8) | int64 | postgres.h |
| boolean | bool | postgres.h (maybe compiler built-in) |
| box | BOX* | utils/geo_decls.h |
| bytea | bytea* | postgres.h |
| “char” | char | (compiler built-in) |
| character | BpChar* | postgres.h |
| cid | CommandId | postgres.h |
| date | DateADT | utils/date.h |
| smallint (int2) | int16 | postgres.h |
| int2vector | int2vector* | postgres.h |
| integer (int4) | int32 | postgres.h |
| real (float4) | float4* | postgres.h |
| double precision (float8) | float8* | postgres.h |
| interval | Interval* | datatype/timestamp.h |
| lseg | LSEG* | utils/geo_decls.h |
| name | Name | postgres.h |
| oid | Oid | postgres.h |
| oidvector | oidvector* | postgres.h |
| path | PATH* | utils/geo_decls.h |
| point | POINT* | utils/geo_decls.h |
| regproc | regproc | postgres.h |
| reltime | RelativeTime | utils/nabstime.h |
| text | text* | postgres.h |
| tid | ItemPointer | storage/itemptr.h |
| time | TimeADT | utils/date.h |
| time with time zone | TimeTzADT | utils/date.h |
| timestamp | Timestamp* | datatype/timestamp.h |
| tinterval | TimeInterval | utils/nabstime.h |
| varchar | VarChar* | postgres.h |
| xid | TransactionId | postgres.h |
transform 例子程式碼
下面是一個transform的例子
CREATE TRANSFORM FOR int LANGUAGE SQL (
FROM SQL WITH FUNCTION varchar_transform(internal),
TO SQL WITH FUNCTION int4recv(internal));
varchar_transform@src/backend/utils/adt/varchar.c
/*
* varchar_transform()
* Flatten calls to varchar`s length coercion function that set the new maximum
* length >= the previous maximum length. We can ignore the isExplicit
* argument, since that only affects truncation cases.
*/
Datum
varchar_transform(PG_FUNCTION_ARGS)
{
FuncExpr *expr = (FuncExpr *) PG_GETARG_POINTER(0);
Node *ret = NULL;
Node *typmod;
Assert(IsA(expr, FuncExpr));
Assert(list_length(expr->args) >= 2);
typmod = (Node *) lsecond(expr->args);
if (IsA(typmod, Const) &&!((Const *) typmod)->constisnull)
{
Node *source = (Node *) linitial(expr->args);
int32 old_typmod = exprTypmod(source);
int32 new_typmod = DatumGetInt32(((Const *) typmod)->constvalue);
int32 old_max = old_typmod - VARHDRSZ;
int32 new_max = new_typmod - VARHDRSZ;
if (new_typmod < 0 || (old_typmod >= 0 && old_max <= new_max))
ret = relabel_to_typmod(source, new_typmod);
}
PG_RETURN_POINTER(ret);
}
int4recv@src/backend/utils/adt/int.c
/*
* int4recv - converts external binary format to int4
*/
Datum
int4recv(PG_FUNCTION_ARGS)
{
StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
PG_RETURN_INT32((int32) pq_getmsgint(buf, sizeof(int32)));
}
建立服務端函式
函式是資料庫的擴充套件基礎，比如你要在PostgreSQL上擴充套件新的資料型別，以及新的資料庫索引方法、操作符等，必須先建立FUNCTION。
https://www.postgresql.org/docs/devel/static/sql-createfunction.html
當我們使用create extension plxx;建立好了資料庫擴充套件語言後，就可以使用對應的擴充套件語言建立對應的資料庫函式了。
目前PostgreSQL內建的包括C, plpgsql, SQL, plpython, plperl, pltcl。
固定的建立函式格式如下
create or replace function 函式名(引數名 引數型別,....) returns [setof] 返回型別 as
$$
...擴充套件語言的程式碼, 根據對應的語言語法來寫...
$$
language 擴充套件語言(如plpgsql) ;
例子
CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS
$$
#variable_conflict use_variable
DECLARE
curtime timestamp := now();
BEGIN
UPDATE users SET last_modified = curtime, comment = comment
WHERE users.id = id;
END;
$$ LANGUAGE plpgsql;
建立函式的完整語法如下
CREATE [ OR REPLACE ] FUNCTION
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
[ RETURNS rettype
| RETURNS TABLE ( column_name column_type [, ...] ) ]
{ LANGUAGE lang_name
| TRANSFORM { FOR TYPE type_name } [, ... ]
| WINDOW
| IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
| PARALLEL { UNSAFE | RESTRICTED | SAFE }
| COST execution_cost
| ROWS result_rows
| SET configuration_parameter { TO value | = value | FROM CURRENT }
| AS `definition`
| AS `obj_file`, `link_symbol`
} ...
[ WITH ( attribute [, ...] ) ]
普通函式
例子
CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS
$$
#variable_conflict use_variable
DECLARE
curtime timestamp := now();
BEGIN
UPDATE users SET last_modified = curtime, comment = comment
WHERE users.id = id;
END;
$$ LANGUAGE plpgsql;
視窗函式
視窗函式可用於支援視窗查詢，視窗查詢的例子可以參考
《用PostgreSQL找回618秒逝去的青春 – 遞迴收斂優化》
例子
CREATE FUNCTION nth_value_def(val anyelement, n integer = 1) RETURNS anyelement
LANGUAGE internal WINDOW IMMUTABLE STRICT AS `window_nth_value`;
src/backend/utils/adt/windowfuncs.c
/*
* nth_value
* return the value of VE evaluated on the n-th row from the first
* row of the window frame, per spec.
*/
Datum
window_nth_value(PG_FUNCTION_ARGS)
{
WindowObject winobj = PG_WINDOW_OBJECT();
bool const_offset;
Datum result;
bool isnull;
int32 nth;
nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
if (isnull)
PG_RETURN_NULL();
const_offset = get_fn_expr_arg_stable(fcinfo->flinfo, 1);
if (nth <= 0)
ereport(ERROR,
(errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTH_VALUE),
errmsg("argument of nth_value must be greater than zero")));
result = WinGetFuncArgInFrame(winobj, 0,
nth - 1, WINDOW_SEEK_HEAD, const_offset,
&isnull, NULL);
if (isnull)
PG_RETURN_NULL();
PG_RETURN_DATUM(result);
}
聚合函式
聚合在資料分析，統計的應用場景中非常場景，比如count, avg, sum, …。
聚合函式開發的例子
《performance tuning about multi-rows query aggregated to single-row query》
《PostgreSQL aggregate function customize》
《hll外掛在Greenplum中的使用 以及 分散式聚合函式優化思路》
《Postgres-XC customized aggregate introduction》
PostgreSQL程式碼中的自定義聚合例子
CREATE AGGREGATE newavg (
sfunc = int4_avg_accum, basetype = int4, stype = _int8,
finalfunc = int8_avg,
initcond1 = `{0,0}`
);
其中sfunc int4_avg_accum和finalfunc int8_avg函式如下
int4_avg_accum用於累加（包括計數和sum），int8_avg則在最後一步時做了一次除法得到avg
src/backend/utils/adt/numeric.c
Datum
int4_avg_accum(PG_FUNCTION_ARGS)
{
ArrayType *transarray;
int32 newval = PG_GETARG_INT32(1);
Int8TransTypeData *transdata;
/*
* If we`re invoked as an aggregate, we can cheat and modify our first
* parameter in-place to reduce palloc overhead. Otherwise we need to make
* a copy of it before scribbling on it.
*/
if (AggCheckCallContext(fcinfo, NULL))
transarray = PG_GETARG_ARRAYTYPE_P(0);
else
transarray = PG_GETARG_ARRAYTYPE_P_COPY(0);
if (ARR_HASNULL(transarray) ||
ARR_SIZE(transarray) != ARR_OVERHEAD_NONULLS(1) + sizeof(Int8TransTypeData))
elog(ERROR, "expected 2-element int8 array");
transdata = (Int8TransTypeData *) ARR_DATA_PTR(transarray);
transdata->count++;
transdata->sum += newval;
PG_RETURN_ARRAYTYPE_P(transarray);
}
Datum
int8_avg(PG_FUNCTION_ARGS)
{
ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0);
Int8TransTypeData *transdata;
Datum countd,
sumd;
if (ARR_HASNULL(transarray) ||
ARR_SIZE(transarray) != ARR_OVERHEAD_NONULLS(1) + sizeof(Int8TransTypeData))
elog(ERROR, "expected 2-element int8 array");
transdata = (Int8TransTypeData *) ARR_DATA_PTR(transarray);
/* SQL defines AVG of no values to be NULL */
if (transdata->count == 0)
PG_RETURN_NULL();
countd = DirectFunctionCall1(int8_numeric,
Int64GetDatumFast(transdata->count));
sumd = DirectFunctionCall1(int8_numeric,
Int64GetDatumFast(transdata->sum));
PG_RETURN_DATUM(DirectFunctionCall2(numeric_div, sumd, countd));
}
觸發器函式
使用擴充套件語言，除了可以用來編寫普通函式，聚合、視窗函式，還可以用於編寫觸發器函式，事件觸發器函式。
下面是使用plpgsql編寫觸發器函式的例子
事件觸發器函式
下面是使用plpgsql編寫事件觸發器的例子
《PostgreSQL 事件觸發器 – PostgreSQL 9.3 Event Trigger》
plpgsql的使用
下一篇文章開始介紹plpgsql的使用
相關文章
- 資料庫擴充套件表設計過程記錄資料庫套件
- 可動態擴充套件的資料庫模型設計套件資料庫模型
- PostgreSQL在雲端：部署、管理和擴充套件你的資料庫SQL套件資料庫
- 如何設計一門語言（十二）——設計可擴充套件的型別套件型別
- ASP.NET Core擴充套件庫之Http通用擴充套件ASP.NET套件HTTP
- 基於PostgreSQL各種擴充套件派生的開源資料庫名單SQL套件資料庫
- PostgreSQL 安裝擴充套件包SQL套件
- XML - Schema之資料型別擴充套件XML資料型別套件
- C語言程式設計之《資料輸出》C語言程式設計
- PHP 資料庫擴充之 PDOPHP資料庫
- 程式設計之美:螞蟻爬杆問題的擴充套件程式設計套件
- GBASE觀察：擴充套件分析型資料庫套件資料庫
- C++11語言擴充套件：常規特性C++套件
- XBRL(可擴充套件商業報告語言套件
- 用C語言擴充套件Python的功能C語言套件Python
- Go 語言編寫 CPython 擴充套件 goPyGoPython套件
- 大資料——Scala擴充套件大資料套件
- 資料塊、資料擴充套件、段套件
- Chrome瀏覽器擴充套件開發系列之八：Chrome擴充套件的資料儲存Chrome瀏覽器套件
- ReactiveUI是.NET的Reactive程式設計擴充套件框架ReactUI程式設計套件框架
- PHP的SPL擴充套件庫（一）資料結構PHP套件資料結構
- C++對C語言的擴充套件(1)--引用C++C語言套件
- SQL Server資料庫檔案不滿足擴充套件條件時不再自動擴充套件SQLServer資料庫套件
- 資料檢索擴充套件包套件
- 擴充套件資料檔案大小套件
- java資料型別擴充套件Java資料型別套件
- 第二十一章：語言擴充套件和程式標註套件
- INFORMIX表的預設初始擴充套件、下一個擴充套件資料塊以及一個表允許的最大擴充套件數。ORM套件
- [譯]AppExtension程式設計指南:擴充套件基礎4APP程式設計套件
- [譯]AppExtension程式設計指南:擴充套件基礎1APP程式設計套件
- [譯]AppExtension程式設計指南:擴充套件基礎2APP程式設計套件
- [譯]AppExtension程式設計指南:擴充套件基礎3APP程式設計套件
- 使用nub恢復資料庫的知識擴充套件資料庫套件
- MySQL資料庫對GROUP BY子句的功能擴充套件(2)MySql資料庫套件
- MySQL資料庫對GROUP BY子句的功能擴充套件(1)MySql資料庫套件
- Swift 小貼士：語言的擴充套件和自定義Swift套件
- 可擴充套件客戶資訊語言 (xCIL) 簡介 (轉)套件
- C語言程式設計之《從鍵盤輸入資料》C語言程式設計