I l @ ve RuBoard |
Most acts of extension require the defining of special functions. For instance, to define a new data type, a C shared-object function describing the new data type must first be created. There are three fundamental types of custom function (also refer to Chapter 12, "Creating Custom Functions," for a more relevant discussion of created SQL or PL functions):
SQL FunctionsSQL language functions are simply predefined queries that are assigned a name . However, they do support input type and can provide return values. Writing SQL functions requires no modification to the base system or special features. For instance: CREATE FUNCTION getpay(int4) RETURN float8 AS ' SELECT sum((amount) FROM payroll WHERE employee_id=; ' LANGUAGE 'sql'; Standard SQL functions can also handle classes to be passed to or from it. For instance: CREATE FUNCTION getshortname(payroll) RETURN varchar AS ' SELECT left((.last_name, 4) AS S_Name; ' LANGUAGE 'sql'; SELECT last_name FROM payroll WHERE emp_id=12345; last_name ------------------- Parody SELECT getshortname(payroll) WHERE emp_id=12345; last_name ------------------- Paro Procedural Language FunctionsProcedural language functions are offered via loadable modules. For instance, the PL/pgSQL language depends on the plpgsql.so loadable module. After these shared objects have been created, they are defined as handlers with the CREATE LANGUAGE command. The specific steps to create a valid handler object are beyond the scope of this book, but the basic or general steps would be as follows :
After a language has been defined, functions and stored procedures can be created with it. Currently, PostgreSQL supports PL/pgSQL, PL/Tcl, and PL/Perl. For more information on creating procedural language functions, refer to Chapter 11, "Server-Side Programming." Compiled FunctionsCompiled functions are shared objects that have been registered with the database through the use of the CREATE FUNCTION command. Creating custom compiled functions is more complex than creating scripted functions, but they do offer a tremendous benefit in execution speed. Creating successful C functions requires that the PostgreSQL and C data types can be exchanged correctly. Table 14.2 lists the PostgreSQL data type, the corresponding C data type, and the C header file where it is defined. Table 14.2. Corresponding PostgreSQL Data Types, C Data Types, and C Header Files
Data is passed to the compiled function internally in one of three ways:
Generally, data that is passed by value must either be 1, 2, or 4 bytes in length (although some architectures can support 8 bytes as well). Fixed-length or variable-length calls can be made with any size data types. Calling a C-Based FunctionTwo separate conventions exist regarding how C-based functions are to be interfaced :
BecauseVersion-0 calling is now deprecated, the following examples will demonstrate some simpleVersion-1 functions. (For more information onVersion-0 calling, refer to the PostgreSQL Programmer's Guide at www.postgresql.org.) Version-1 “compliant functions should begin with two macros: PG_FUNCTION_INFO_V1 and PG_FUNCTION_ARGS . The following is a simple pass-by-value example: /* Program Name: add_it.c Description: Adds two int32 numbers */ #include "postgres.h" #include "fmgr.h" PG_FUNCTION_INFO_V1(add_it); Datum add_it(PG_FUNCTION_ARGS) { int32 arg1 = PG_GETARG_INT32(0); int32 arg2 = PG_GETARG_INT32(1); PG_RETURN_INT32(arg1 + arg2); } After this has been compiled into a shared object, it can be defined and utilized as follows: CREATE FUNCTION add_it(int4) RETURNS int4 AS '/usr/local/pgsql/lib/add_it.so' LANGUAGE 'C'; >SELECT add_it(4, 8) AS Answer; Answer ------ 12 In addition to handling simple pass-by-value transfers, composite objects, like row objects, can be passed and manipulated by C functions. For instance, this example defines the function named isminor() , which returns TRUE or FALSE depending on whether the employee is 21 or over: /* Program Name: islegal.c Description: Determines if an employee is legal age */ #include "postgres.h" #include "executor/executor.h" #include "fmgr.h" PG_FUNCTION_INFO_V1(islegal); Datum islegal(PG_FUNCTION_ARGS) { /*Get the current table row, assign to pointer t*/ TupleTableSlot *t = (TupleTableSlot *) PG_GETARG_POINTER(0); /*Declare variables needed*/ int32 emp_age; bool isnull; /*Get the 'age' attribute from the row, this function defined in executor.h*/ emp_age = DatumGetInt32(GetAttributeByName(t, "age", &isnull)); /*If not a valid result, return NULL*/ if (isnull) { PG_RETURN_NULL(); } /*Return Age Comparison*/ PG_RETURN_BOOL(age>20); } After this function is compiled to a shared object, it can be defined and used within PostgreSQL. For instance: CREATE FUNCTION islegal(payroll) RETURNS bool AS '/usr/local/pgsql/lib/islegal.so' LANGUAGE 'C'; >SELECT islegal(payroll) FROM payroll WHERE name='Barry'; islegal ------ t Coding Tips and TricksThe following is a list of tips and pointers garnered from the PostgreSQL Programmer's Guide (for more information on this guide, visit www.postgresql.org):
|
I l @ ve RuBoard |