Shulou(Shulou.com)06/01 Report--

This section takes the implementation of the add_months function in Oracle as an example to introduce how to implement custom system functions by modifying the kernel.

I. basic knowledge

Before implementation, it is necessary to introduce some basic knowledge, including Oid/ function registration and so on.

Oid

Oid is Object identifier, object identifier. In PostgreSQL, each object has an Oid, and the system table objects are associated with Oid.

Function as an object in PostgreSQL, each function has Oid. The relevant information can be obtained by querying 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-- |

Function registration

Suppose we have implemented a custom system function, such as how can add_months,PostgreSQL perceive the existence of this function? The answer is through function registration.

When PostgreSQL compiles, it uses the perl script to generate a src/backend/catalog/postgres.bki file according to the preset records. The file is parsed into SQL bars during initdb and inserted into the system table. Therefore, custom system functions can only be perceived in the new database instance created through initdb.

II. Implementation steps

With the above basic knowledge, let's implement the add_months custom function in step by step.

1. Get the function Oid

PostgreSQL provides a unused_oids tool for quickly retrieving unused Oid, which is located in the src/include/catalog directory

Find-name unused_oids./src/include/catalog/unused_ oids [root @ localhost pg11] #. / src/include/catalog/unused_oids2-93423-3436399639984001-40134142-41994217-45654572-49995017-50275029-59996015-6099610361056107-610961166122-9999

We chose Oid = 5100

two。 Register function

Add the add_months function to the file pg_proc.dat

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

The entries in this file correspond to the structure Form_pg_proc

/ *-* pg_proc definition. Cpp turns this into * typedef struct FormData_pg_proc * pg_proc definition *-* / CATALOG (pg_proc,1255,ProcedureRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID (81 magnets ProcedureRelationalization Rowtypewriter ID) BKI_SCHEMA_MACRO {/ * procedure name * / / process name NameData proname / * OID of namespace containing this proc * / / system OID Oid pronamespace BKI_DEFAULT (PGNSP); / * procedure owner * / / owner Owner Oid proowner BKI_DEFAULT (PGUID); / * OID of pg_language entry * / / implementation language call interface, OID in pg_language. / / defaults to 12-internal. Other options include 13murc language, 14MySQL Oid prolang BKI_DEFAULT (12); / * estimated execution cost * / / estimated execution cost, default is 1 float4 procost BKI_DEFAULT (1); / * estimated # of rows out (if proretset) * / / estimated number of resulting rows, default is 0 float4 prorows BKI_DEFAULT (0) / * element type of variadic array, or 0 * / / variable array parameter element type, default is 0 Oid provariadic BKI_DEFAULT (0) BKI_LOOKUP (pg_type); / * transforms calls to it during planning * / / conversion calls during the plan period, default is 0 / / regproc protransform BKI_DEFAULT (0) BKI_LOOKUP (pg_proc) can be simplified by the function specified in this column / * see PROKIND_ categories below * / / see PROKIND_XXX char prokind BKI_DEFAULT (f) below; / * security definer * / / Security definer bool prosecdef BKI_DEFAULT (f); / * is it a leak-proof function? * / / weak authentication function? In addition to the return value, the information with no relational parameters is propagated bool proleakproof BKI_DEFAULT (f); / * strict with respect to NULLs? * / / NULLs processing (strict or loose) bool proisstrict BKI_DEFAULT (t); / * returns a set? * / / returns the collection? Default is F bool proretset BKI_DEFAULT (f); / * see PROVOLATILE_ categories below * / see PROVOLATILE_XXX char provolatile BKI_DEFAULT (I) below for details; / * see PROPARALLEL_ categories below * / see PROPARALLEL_XXX char proparallel BKI_DEFAULT (s) below; / * number of arguments * / / * Note: need not be given in pg_proc.dat Genbki.pl will compute it * / / number of parameters / / Note: there is no need to specify in pg_proc.dat. Genbki.pl will automatically calculate the number of parameters with default values in int16 pronargs; / * number of arguments with defaults * / / int16 pronargdefaults BKI_DEFAULT (0); / * OID of result type * / / result type OID Oid prorettype BKI_LOOKUP (pg_type) / * * variable-length fields start here, but we allow direct access to * proargtypes * is a variable length field from here, but we run the direct access prototype type * / * parameter types (excludes OUT params) * / / Parameter type (excluding OUT parameter) / / includes only input parameters (including INOUT and VARIADIC parameters oidvector proargtypes BKI_LOOKUP (pg_type)) # ifdef CATALOG_VARLEN / * all param types (NULL if IN only) * / / all parameter types (array), including all parameters (including OUT and INOUT parameters) / / if all parameters are of IN type, NULL Oid proallargtypes [1] BKI_DEFAULT (_ null_) BKI_LOOKUP (pg_type) / * parameter modes (NULL if IN only) * / / Parameter pattern array (NULL if all IN parameters) / / I represents the IN parameter, o represents the OUT parameter, b represents the INOUT parameter, v represents the VARIADIC parameter, t represents the TABLE parameter char proargmodes [1] BKI_DEFAULT (_ null_) / * parameter names (NULL if no names) * / / Parameter name array (NULL if none) / / the subscript here corresponds to proallargtypes instead of the position text proargnames [1] BKI_DEFAULT (_ null_) in proargtypes / * list of expression trees for argument defaults (NULL if none) * / / Parameter default expression tree linked list (NULL if none) / / corresponding proargtypes pg_node_tree proargdefaults BKI_DEFAULT (_ null_); / * types for which to apply transforms * / / the type Oid protrftypes [1] BKI_DEFAULT (_ null_) to which the transformation is applied / * procedure source text * / / procedure implementation text (function name if c) text prosrc BKI_FORCE_NOT_NULL; / * secondary procedure info (can be NULL) * / / the second procedure information, that is, additional information (which can be NULL) text probin BKI_DEFAULT (_ null_) / * procedure-local GUC settings * / / process-related local GUC settings text proconfig [1] BKI_DEFAULT (_ null_); / * access permissions * / / access rights 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' 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'/ * 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 * /

Through the perl script, PG generates the entries in the definition file into a postgres.bki file and looks at the comments in the Makefile file (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中的实现( 感谢开源! ) Datumadd_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);} intdays_of_month (int y, int m) {int month_days [] = {31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31}; intdays 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;}

Add a function declaration to the header file src/include/utils/date.h

Extern Datum add_months (PG_FUNCTION_ARGS)

4. Compilation and installation

Make cleanmakemake install

5. Initialize the database

Initdb-D / data/pgsql/tmpdbpg_ctl start-D / data/pgsql/tmpdb

6. Check and verify

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

DONE!

III. Reference materials

Oid

Orafce

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.