本節以實現Oracle中的add_months函數為例介紹如何通過改造內核實現自定義系統函數.

一、基礎知識

在實現之前有必要先行介紹一些基礎知識,包括Oid/函數注冊等.
Oid
Oid即Object identifier,對象標識符,在PostgreSQL中,每個對象都一個Oid,系統表對象之間以Oid進行關聯.
函數作為PostgreSQL中的一種對象,每個函數都存在Oid,通過查詢pg_proc可獲得相關信息:

postgres=# select oid,proname from pg_proc order by oid;
  oid  |                   proname                    
-------+----------------------------------------------
    31 | byteaout
    33 | charout
    34 | namein
    35 | nameout
    38 | int2in
    39 | int2out
    40 | int2vectorin
    41 | int2vectorout
    42 | int4in
    43 | int4out
    44 | regprocin
    45 | regprocout
    46 | textin
    47 | textout
    48 | tidin
    49 | tidout
    50 | xidin
    51 | xidout
    52 | cidin
    53 | cidout
    54 | oidvectorin
    55 | oidvectorout
    56 | boollt
    57 | boolgt
    60 | booleq
--More--

函數注冊
假設我們已經實現了一個自定義系統函數,比如add_months,PostgreSQL如何才能感知該函數的存在?答案是通過函數注冊實現.
PostgreSQL在編譯的時候,會用perl腳本根據預置的記錄,生成src/backend/catalog/postgres.bki文件,該文件在initdb時被解析成一條條的SQL,插入到系統表中.因此自定義的系統函數,需要在通過initdb新建的數據庫實例中才能被”感知”.

二、實現步驟

有了上面的基礎知識,接下來我們step by step的實現add_months自定義函數.
1.獲取函數Oid
PostgreSQL提供了unused_oids工具用于快速檢索未使用的Oid,該文件位于src/include/catalog目錄下

find -name unused_oids
./src/include/catalog/unused_oids
[root@localhost pg11]# ./src/include/catalog/unused_oids
2 - 9
3423 - 3436
3996
3998
4001 - 4013
4142 - 4199
4217 - 4565
4572 - 4999
5017 - 5027
5029 - 5999
6015 - 6099
6103
6105
6107 - 6109
6116
6122 - 9999

我們選擇了Oid = 5100

2.注冊函數
在文件pg_proc.dat中添加add_months函數

#src/include/catalog/pg_proc.dat
...
{ oid => '5100', descr => 'oracle-like add_months function',
  proname => 'add_months', provariadic => '0',
  proisstrict => 'f', prorettype => 'date', proargtypes => 'date int4',
  prosrc => 'add_months'},

該文件中的條目對應結構體Form_pg_proc

/* ----------------
 *        pg_proc definition.  cpp turns this into
 *        typedef struct FormData_pg_proc
  *        pg_proc定義
* ----------------
 */
CATALOG(pg_proc,1255,ProcedureRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(81,ProcedureRelation_Rowtype_Id) BKI_SCHEMA_MACRO
{
    /* procedure name */
    //過程名稱
    NameData    proname;
    /* OID of namespace containing this proc */
    //系統OID
    Oid            pronamespace BKI_DEFAULT(PGNSP);
    /* procedure owner */
    //擁有者Owner
    Oid            proowner BKI_DEFAULT(PGUID);
    /* OID of pg_language entry */
    //實現語言調用接口,pg_language中的OID.
    //默認為12-internal,其他選項包括13-c語言,14-sql,13275-plpgsql
    Oid            prolang BKI_DEFAULT(12);
    /* estimated execution cost */
    //估算的執行成本,默認為1
    float4        procost BKI_DEFAULT(1);
    /* estimated # of rows out (if proretset) */
    //估算的結果行數,默認為0
    float4        prorows BKI_DEFAULT(0);
    /* element type of variadic array, or 0 */
    //可變數組參數元素類型,默認為0
    Oid            provariadic BKI_DEFAULT(0) BKI_LOOKUP(pg_type);
    /* transforms calls to it during planning */
    //在計劃期間的轉換調用,默認為0
    //可通過此列指定的函數來簡化
    regproc        protransform BKI_DEFAULT(0) BKI_LOOKUP(pg_proc);
    /* see PROKIND_ categories below */
    //詳見下面的PROKIND_XXX
    char        prokind BKI_DEFAULT(f);
    /* security definer */
    //安全定義器
    bool        prosecdef BKI_DEFAULT(f);
    /* is it a leak-proof function? */
    //弱認證函數?除了返回值,沒有關系參數的信息被傳播
    bool        proleakproof BKI_DEFAULT(f);
    /* strict with respect to NULLs? */
    //NULLs的處理(嚴格還是不嚴格)
    bool        proisstrict BKI_DEFAULT(t);
    /* returns a set? */
    //返回集合?默認為F
    bool        proretset BKI_DEFAULT(f);
    /* see PROVOLATILE_ categories below */
    //詳見下面的PROVOLATILE_XXX
    char        provolatile BKI_DEFAULT(i);
    /* see PROPARALLEL_ categories below */
    //詳見下面的PROPARALLEL_XXX
    char        proparallel BKI_DEFAULT(s);
    /* number of arguments */
    /* Note: need not be given in pg_proc.dat; genbki.pl will compute it */
    //參數個數
    //注意:不需要在pg_proc.dat中指定,genbki.pl會自動計算
    int16        pronargs;
    /* number of arguments with defaults */
    //有默認值的參數個數
    int16        pronargdefaults BKI_DEFAULT(0);
    /* OID of result type */
    //結果類型OID
    Oid            prorettype BKI_LOOKUP(pg_type);
    /*
     * variable-length fields start here, but we allow direct access to
     * proargtypes
     * 從這里開始為可變長字段,但我們運行直接訪問原型類型
     */
    /* parameter types (excludes OUT params) */
    //參數類型(剔除了OUT參數)
    //只包括輸入參數（含INOUT和VARIADIC參數
    oidvector    proargtypes BKI_LOOKUP(pg_type);
#ifdef CATALOG_VARLEN
    /* all param types (NULL if IN only) */
    //所有參數類型(數組),包括所有參數（含OUT和INOUT參數）
    //如都為IN類型,則為NULL
    Oid            proallargtypes[1] BKI_DEFAULT(_null_) BKI_LOOKUP(pg_type);
    /* parameter modes (NULL if IN only) */
    //參數模式數組(如都為IN參數,則為NULL)
    // i表示IN參數 ， o表示OUT參數， b表示INOUT參數， v表示VARIADIC參數， t表示TABLE參數
    char        proargmodes[1] BKI_DEFAULT(_null_);
    /* parameter names (NULL if no names) */
    //參數名稱數組(如無則為NULL)
    //這里的下標對應著proallargtypes而不是proargtypes中的位置
    text        proargnames[1] BKI_DEFAULT(_null_);
    /* list of expression trees for argument defaults (NULL if none) */
    //參數默認值表達式樹鏈表(如無則為NULL)
    //對應proargtypes
    pg_node_tree proargdefaults BKI_DEFAULT(_null_);
    /* types for which to apply transforms */
    //應用變換的類型
    Oid            protrftypes[1] BKI_DEFAULT(_null_);
    /* procedure source text */
    //過程實現文本(如為c,則可為函數名稱)
    text        prosrc BKI_FORCE_NOT_NULL;
    /* secondary procedure info (can be NULL) */
    //第二個過程信息,即附加信息(可為NULL)
    text        probin BKI_DEFAULT(_null_);
    /* procedure-local GUC settings */
    //與過程相關的本地GUC設置
    text        proconfig[1] BKI_DEFAULT(_null_);
    /* access permissions */
    //訪問權限
    aclitem        proacl[1] BKI_DEFAULT(_null_);
#endif
} FormData_pg_proc;
/* ----------------
 *        Form_pg_proc corresponds to a pointer to a tuple with
 *        the format of pg_proc relation.
 * ----------------
 */
typedef FormData_pg_proc *Form_pg_proc;
#ifdef EXPOSE_TO_CLIENT_CODE
/*
 * Symbolic values for prokind column
 */
#define PROKIND_FUNCTION 'f'
#define PROKIND_AGGREGATE 'a'
#define PROKIND_WINDOW 'w'
#define PROKIND_PROCEDURE 'p'
/*
 * Symbolic values for provolatile column: these indicate whether the result
 * of a function is dependent *only* on the values of its explicit arguments,
 * or can change due to outside factors (such as parameter variables or
 * table contents).  NOTE: functions having side-effects, such as setval(),
 * must be labeled volatile to ensure they will not get optimized away,
 * even if the actual return value is not changeable.
 */
#define PROVOLATILE_IMMUTABLE    'i' /* never changes for given input */
#define PROVOLATILE_STABLE        's' /* does not change within a scan */
#define PROVOLATILE_VOLATILE    'v' /* can change even within a scan */
/*
 * Symbolic values for proparallel column: these indicate whether a function
 * can be safely be run in a parallel backend, during parallelism but
 * necessarily in the master, or only in non-parallel mode.
 */
#define PROPARALLEL_SAFE        's' /* can run in worker or master */
#define PROPARALLEL_RESTRICTED    'r' /* can run in parallel master only */
#define PROPARALLEL_UNSAFE        'u' /* banned while in parallel mode */
/*
 * Symbolic values for proargmodes column.  Note that these must agree with
 * the FunctionParameterMode enum in parsenodes.h; we declare them here to
 * be accessible from either header.
 */
#define PROARGMODE_IN        'i'
#define PROARGMODE_OUT        'o'
#define PROARGMODE_INOUT    'b'
#define PROARGMODE_VARIADIC 'v'
#define PROARGMODE_TABLE    't'
#endif                            /* EXPOSE_TO_CLIENT_CODE */

通過perl腳本,PG會把該定義文件中的條目生成postgres.bki文件,查看Makefile文件(src/backend/catalog/Makefile)中的注釋:

# bki-stamp records the last time we ran genbki.pl.  We don't rely on
# the timestamps of the individual output files, because the Perl script
# won't update them if they didn't change (to avoid unnecessary recompiles).
# Technically, this should depend on Makefile.global which supplies
# $(MAJORVERSION); but then genbki.pl would need to be re-run after every
# configure run, even in distribution tarballs.  So depending on configure.in
# instead is cheating a bit, but it will achieve the goal of updating the
# version number when it changes.
bki-stamp: genbki.pl Catalog.pm $(POSTGRES_BKI_SRCS) $(POSTGRES_BKI_DATA) $(top_srcdir)/configure.in
    $(PERL) -I $(catalogdir) $< --set-version=$(MAJORVERSION) $(POSTGRES_BKI_SRCS)
    touch $@

編譯成功后,生成的src/backend/catalog/postgres.bki中包含了我們添加的條目(OID = 5100):

...
insert OID = 5028 ( satisfies_hash_partition 11 10 12 1 0 2276 0 f f f f f i s 4 0 16 "26 23 23 2276" _null_ "{i,i,i,v}" _null_ _null_ _null_ satisfies_hash_partition _null_ _null_ _null_ )
insert OID = 5100 ( add_months 11 10 12 1 0 0 0 f f f f f i s 2 0 1082 "1082 23" _null_ _null_ _null_ _null_ _null_ add_months _null_ _null_ _null_ )
close pg_proc
...

3.實現功能
在src/backend/utils/adt/date.c文件的最后添加邏輯實現.
該實現使用了Github開源項目orafce中的實現( 感謝開源! )

Datum
add_months(PG_FUNCTION_ARGS)
{
    DateADT day = PG_GETARG_DATEADT(0);
    int n = PG_GETARG_INT32(1);
    int y, m, d;
    int    days;
    DateADT result;
    div_t    v;
    bool    last_day;
    j2date(day + POSTGRES_EPOCH_JDATE, &y, &m, &d);
    last_day = (d == days_of_month(y, m));
    v = div(y * 12 + m - 1 + n, 12);
    y = v.quot;
    if (y < 0)
        y += 1;    /* offset because of year 0 */
    m = v.rem + 1;
    days = days_of_month(y, m);
    if (last_day || d > days)
        d = days;
    result = date2j(y, m, d) - POSTGRES_EPOCH_JDATE;
    PG_RETURN_DATEADT (result);
}
int
days_of_month(int y, int m)
{
    int month_days[] = {31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31};
    int    days;
    if (m < 0 || 12 < m)
        ereport(ERROR,
                (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
                 errmsg("date out of range")));
    days = month_days[m - 1];
    if (m == 2 && (y % 400 == 0 || (y % 4 == 0 && y % 100 != 0)))
        days += 1;    /* February 29 in leap year */
    return days;
}

在頭文件src/include/utils/date.h中添加函數聲明

extern Datum add_months(PG_FUNCTION_ARGS);

4.編譯安裝

make clean
make
make install

5.初始化數據庫

initdb -D /data/pgsql/tmpdb
pg_ctl start -D /data/pgsql/tmpdb

6.檢查驗證

postgres=# select add_months(current_date,12);
 add_months 
------------
 2020-04-28
(1 row)

DONE!

三、參考資料

Oid
orafce