在开发或者业务需求情况下,可能会新增内置函数,本文将简单介绍如何在pg数据库中自定义内置函数。
举例说明
在真正自定义函数之前,首先要了解pg是如何声明和定义函数的,掌握原理加以模仿就可以根据需要添加内置函数了。熟悉的同学在调试时经常使用该SQL:
select pg_backend_pid();
其中pg_backend_pid()就是一个典型的内置函数,我们来看看pg是如何声明和定义的:
在src/backend/utils/adt/pgstatfuncs.c下:
Datum
pg_backend_pid(PG_FUNCTION_ARGS)
{
PG_RETURN_INT32(MyProcPid);
}
仔细一看,很简单,就是直接返回当前进程的pid号。
入参:PG_FUNCTION_ARGS 固定,pg任何函数入参均PG_FUNCTION_ARGS
返回值类型: Datum
返回值类型根据需要,有多种情况:int32, uint64, bool和char型等
#define PG_RETURN_DATUM(x) return (x)
#define PG_RETURN_INT32(x) return Int32GetDatum(x)
#define PG_RETURN_UINT32(x) return UInt32GetDatum(x)
#define PG_RETURN_INT16(x) return Int16GetDatum(x)
#define PG_RETURN_UINT16(x) return UInt16GetDatum(x)
#define PG_RETURN_CHAR(x) return CharGetDatum(x)
#define PG_RETURN_BOOL(x) return BoolGetDatum(x)
#define PG_RETURN_OID(x) return ObjectIdGetDatum(x)
#define PG_RETURN_POINTER(x) return PointerGetDatum(x)
#define PG_RETURN_CSTRING(x) return CStringGetDatum(x)
#define PG_RETURN_NAME(x) return NameGetDatum(x)
#define PG_RETURN_TRANSACTIONID(x) return TransactionIdGetDatum(x)
/* these macros hide the pass-by-reference-ness of the datatype: */
#define PG_RETURN_FLOAT4(x) return Float4GetDatum(x)
#define PG_RETURN_FLOAT8(x) return Float8GetDatum(x)
#define PG_RETURN_INT64(x) return Int64GetDatum(x)
#define PG_RETURN_UINT64(x) return UInt64GetDatum(x)
/* RETURN macros for other pass-by-ref types will typically look like this: */
#define PG_RETURN_BYTEA_P(x) PG_RETURN_POINTER(x)
#define PG_RETURN_TEXT_P(x) PG_RETURN_POINTER(x)
#define PG_RETURN_BPCHAR_P(x) PG_RETURN_POINTER(x)
#define PG_RETURN_VARCHAR_P(x) PG_RETURN_POINTER(x)
#define PG_RETURN_HEAPTUPLEHEADER(x) return HeapTupleHeaderGetDatum(x)
为了方便管理内置函数,会给每一个函数分配一个 oid,即唯一标识
在src/include/catalog/pg_proc.dat脚本下,与之对应有如下信息
{ oid => '2026', descr => 'statistics: current backend PID',
proname => 'pg_backend_pid', provolatile => 's', proparallel => 'r',
prorettype => 'int4', proargtypes => '', prosrc => 'pg_backend_pid' },
假如新增内置函数,我们如何确定 oid 可供使用呢? 没关系,pg开发人员早已想到相应方法供我们快速获取空闲 oid,执行如下命令:
./src/include/catalog/unused_oids
// 结果
4 - 9
111
388 - 389
560 - 583
786 - 789
811 - 816
2137
4544 - 4565
4642 - 4999
5097 - 5999
6015 - 6097
6099
6105
6107 - 6109
6116
6122 - 6149
6205 - 9999
Patches should use a more-or-less consecutive range of OIDs.
Best practice is to start with a random choice in the range 8000-9999.
Suggested random unused OID: 8472 (1528 consecutive OID(s) available starting here)
实例
通过一个 xid 取消一个事务/进程
/*
* pg_cancel_pid_backend -- cancel backend
*/
Datum pg_cancel_pid_backend(PG_FUNCTION_ARGS)
{
DistributedTransactionId gxid;
int pid;
bool result;
if(!superuser())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
(errmsg("only superuser may access generic file functions"))));
if (PG_ARGISNULL(0))
PG_RETURN_NULL();
gixd = PG_GETARG_INT64(0);
pid = GetPidByGxid(gxid);
Assert(pid > 0);
result = pg_cancel_backend(pid);
PG_RETURN_BOOL(result == SIGNAL_BACKEND_SUCCESS);
}
{ oid => '8472', descr => 'cancel a backend',
proname => 'pg_cancel_pid_backend', prorettype => 'bool',
proargtypes => 'uint64', prosrc => 'pg_cancel_pid_backend' },
最后,重新编译或者 initdb即可在后续流程中使用自己定义的函数来实现特定功能,其中很多特性待大家挖掘 :)。