PostgreSQL資料庫擴充套件語言程式設計之plpgsql-1

德哥發表於2017-01-13
SQL資料庫套件程式設計

標籤

PostgreSQL , plpgsql , server programming , 儲存過程 , 函式 , UDF , create language , plpython , pljava , plr , plperl , pltcl

背景

PostgreSQL是一個開放的資料庫，開發性表現在支援自定義資料型別、索引方法、索引、操作符、聚合、視窗、服務端程式語言等等。

所以我們可以看到在PostgreSQL的生態中有很多貼近業務的用法，比如在PostgreSQL中儲存和處理化學分子、儲存和處理影像、儲存和處理基因資料、儲存和處理文字（包括正則、模糊、全文檢索等技術）、訪問外部任意資料來源等。

接下來我會花一些時間給大家介紹一下PostgreSQL的服務端程式設計，這也是PostgreSQL開放性的表現之一。

通常我們的用法可能是這樣的，程式語言，通過對應的資料庫驅動，連線到資料庫，如果要實現一些資料的處理時，需要將資料下拉到客戶端，在客戶端的語言中進行處理。

pic

而PostgreSQL還可以這樣使用，把程式語言和資料庫融為一體，你可以把程式放到資料庫裡面去執行，這樣的話資料庫幾乎可以做任何事情（只要程式能做的事情，資料庫都可以做）。

pic

它的好處是非常明顯的，你可以把任何程式語言（只要是地球語言），作為它的服務端語言，你可以用它在資料庫中做任何事情，以前你可能總覺得資料庫就是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編寫觸發器函式的例子

《PostgreSQL 觸發器 用法詳解 1》

《PostgreSQL 觸發器 用法詳解 2》

事件觸發器函式

下面是使用plpgsql編寫事件觸發器的例子

《PostgreSQL 事件觸發器 – PostgreSQL 9.3 Event Trigger》

plpgsql的使用

下一篇文章開始介紹plpgsql的使用


相關文章