本节以实现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
