进制转换函数

题目：
写一个二进制，八进制，十六进制转换为十进制的函数
要求：

1. 函数有两个参数，参数(1)是要转换为十进制的进制数，参数(2)是标示参数(1)是什么进制(2,8,16标示二进制，八进制，十六进制)。
2. 要有报错信息，比如参数是1012，但参数(2)是2，显然是进制数表示有错误。

系统表 pg_proc 存储关于函数的信息
内部函数在编译之前需要先定义在 pg_proc.h 中，src/include/catalog/pg_proc.h 

CATALOG(pg_proc,1255) BKI_BOOTSTRAP BKI_ROWTYPE_OID(81) BKI_SCHEMA_MACRO
{
	NameData	proname;		/* procedure name */	/* 函数名，sql 中 select 函数名(); */
	Oid			pronamespace;	/* OID of namespace containing this proc */    /* 模式OID */
	Oid			proowner;		/* procedure owner */	/* 用户OID */
	Oid			prolang;		/* OID of pg_language entry */
	float4		procost;		/* estimated execution cost */    /* 估计执行成本 */
	float4		prorows;		/* estimated # of rows out (if proretset) */	/* 结果行估计数 */
	Oid			provariadic;	/* element type of variadic array, or 0 */
	regproc		protransform;	/* transforms calls to it during planning */
	bool		proisagg;		/* is it an aggregate? */	/* 是否为聚集函数 */
	bool		proiswindow;	/* is it a window function? */    /* 是否为窗口函数 */
	bool		prosecdef;		/* security definer */    /* 函数是一个安全定义器，也就是一个“setuid"函数 */
	bool		proleakproof;	/* is it a leak-proof function? */    /* 有无其他影响 */
	bool		proisstrict;	/* strict with respect to NULLs? */		/* 遇到 NULL 值是否直接返回 NULL */
	bool		proretset;		/* returns a set? */	/* 函数返回一个集合 */
	char		provolatile;	/* see PROVOLATILE_ categories below */
	int16		pronargs;		/* number of arguments */		/* 参数个数 */
	int16		pronargdefaults;	/* number of arguments with defaults */		/* 默认参数的个数 */
	Oid			prorettype;		/* OID of result type */	/* 返回参数类型OID */

	/*
	 * variable-length fields start here, but we allow direct access to
	 * proargtypes
	 */
	oidvector	proargtypes;	/* parameter types (excludes OUT params) */		/* 存放函数参数类型的数组 */

#ifdef CATALOG_VARLEN
	Oid			proallargtypes[1];		/* all param types (NULL if IN only) */
	char		proargmodes[1]; /* parameter modes (NULL if IN only) */
	text		proargnames[1]; /* parameter names (NULL if no names) */
	pg_node_tree proargdefaults;/* list of expression trees for argument
								 * defaults (NULL if none) */
	Oid			protrftypes[1]; /* types for which to apply transforms */
	text prosrc BKI_FORCE_NOT_NULL;		/* procedure source text */		/* 函数处理器如何调用函数，实现函数的函数名 */
	text		probin;			/* secondary procedure info (can be NULL) */
	text		proconfig[1];	/* procedure-local GUC settings */
	aclitem		proacl[1];		/* access permissions */
#endif
} FormData_pg_proc;

在 proc.h 添加函数定义：

/* myfunc */
DATA(insert OID = 6663 (  x_to_dec 	PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 23 "25 23" _null_ _null_ _null_ _null_ _null_ x_to_dec _null_ _null_ _null_ ));
DESCR("x_to_dec.");

OID = 6663    /* OID 唯一，不能与其他定义 OID 重复 */
x_to_dec    /* sql 中 select x_to_dec(); */
2 0 23 "25 23"    /* 传递两个参数; 默认 0; 返回值类型 OID = 23; 参数1类型 OID = 25, 参数2类型 OID = 23 */
x_to_dec    /* 自定义函数名 */

这里的传递参数类型和返回值类型都用的了 OID

系统表 pg_type 存储数据类型的信息

postgres=# select oid,typname from pg_type where typname = 'text' or typname = 'int4';
 oid | typname 
-----+---------
  23 | int4
  25 | text
(2 rows)

在 src/backend/utils/adt/myfuncs.c 实现自定义的函数

首先创建函数的整体部分：

Datum    /* Datum 类型是PG系统函数大量引用的类型，其定义为：typedef uintptr_c Datum */
x_to_dec (PG_FUNCTION_ARGS)    /* 函数名; 参数 */
{
    /* 获取参数 */
	text *arg1 = PG_GETARG_TEXT_P(0);
	int32 arg2 = PG_GETARG_INT32(1);

    /** 实现功能 **/

    /* 返回 */
	PG_RETURN_INT32(sum);
}

这里的 PG_GETARG_XXXX() 和 PG_RETURN_XXXXX() 在 src/include/fmgr.h

知道了如何获取参数以及返回返回值，接下来是具体的实现：

Datum x_to_dec (PG_FUNCTION_ARGS)
{
	int n = 0, i = 0, sum = 0, t = 0;
	text *arg1 = PG_GETARG_TEXT_P(0);
	int32 arg2 = PG_GETARG_INT32(1);
	char *str = text_to_cstring(arg1);
	n = strlen(str);

	switch(arg2)
	{
		case 2:
			for(i = n - 1; i >= 0; i--)
			{
				if((str[i] - '0') != 1 && (str[i] - '0') != 0)
				{
					ereport(ERROR,
						(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
						errmsg("Please enter the correct binary number, such as '110011'.")));
				}
				sum += (str[i] - '0') * ((int)pow(2, n - 1 - i));
			}
			break;
		case 8:
			for(i = n - 1; i >= 0; i--)
			{
				if(!(str[i] >= '0' && str[i] <= '7'))
				{
					ereport(ERROR,
						(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
						errmsg("Please enter the correct octal number, for example '34567'.")));
				}
				sum += (str[i] - '0') * ((int)pow(8, n - 1 - i));
			}
			break;
		case 16:
			for(i = n - 1; i >= 0; i--)
			{
				if( !(str[i] >= '0' && str[i] <= '9') )
				{
					if(str[i] >= 'A' && str[i] <= 'F')
					{
						// Uppercase to lowercase
						str[i] = str[i] + 32;
					} else if ( !(str[i] >= 'a' && str[i] <= 'f') ) {
						ereport(ERROR,
							(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
							errmsg("Please enter the correct hexadecimal number, for example '9f'.")));
					}
				}
				if(str[i] <= '9')
				{
					t = str[i] - '0';
				} else {
					t = str[i] - 'a' + 10;
				}
				sum = sum * 16 + t;
			}
			break;
		default:
			ereport(ERROR,
				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
				errmsg("Out of range! The second parameter, please enter: 2, 4, 16.")));
	}

	PG_RETURN_INT32(sum);
}

其中用到了text_to_cstring(arg1) ,类型转换的相关函数定义在 src/backend/utils/adt/varlena.c

/*
 * text_to_cstring
 *
 * Create a palloc'd, null-terminated C string from a text value.
 *
 * We support being passed a compressed or toasted text value.
 * This is a bit bogus since such values shouldn't really be referred to as
 * "text *", but it seems useful for robustness.  If we didn't handle that
 * case here, we'd need another routine that did, anyway.
 */
char *
text_to_cstring(const text *t)
{
	/* must cast away the const, unfortunately */
	text	   *tunpacked = pg_detoast_datum_packed((struct varlena *) t);
	int			len = VARSIZE_ANY_EXHDR(tunpacked);
	char	   *result;

	result = (char *) palloc(len + 1);
	memcpy(result, VARDATA_ANY(tunpacked), len);
	result[len] = '\0';

	if (tunpacked != t)
		pfree(tunpacked);

	return result;
}

结果：

postgres=# select x_to_dec('111',2);
 x_to_dec 
----------
        7
(1 row)

postgres=# select x_to_dec('aA',16);
 x_to_dec 
----------
      170
(1 row)

postgres=# select x_to_dec('aA',1);
ERROR:  Out of range! The second parameter, please enter: 2, 4, 16.

 

转载于:https://my.oschina.net/yonj1e/blog/869121