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

**函式註冊**
假設我們已經實現了一個自定義系統函式,比如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](https://www.postgresql.org/docs/current/datatype-oid.html)
[orafce](https://github.com/orafce/orafce)

PostgreSQL 原始碼解讀（180）- 核心研發#4(如何實現自定義系統函式)

相關文章