Extending Functions

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 functions. These functions consist purely of standard SQL code. No external database objects must exist in order for these to be executed. They can be defined on-the-fly regardless of the configuration of the base system.

  • PL functions. These functions are written in a non-native code (for example, PL/pgTCL). For these functions to execute, an external shared-object handler must exist. The handler functions must first be registered with the database back end before execution can proceed.

  • Compiled functions. These functions are typically compiled C-language func-tions. Typically, these functions define a specific input-output response. They can be called from standard SQL code (the upper() function is an example). However, they must first be defined as a C shared object and then registered with the database in order to be activated.

SQL Functions

SQL 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 Functions

Procedural 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 :

  1. Compile the shared-object handler from C (for example, plfoobar.so ).

  2. Create a function that defines this object. The return type must be set as OPAQUE for this function. For instance:

     CREATE FUNCTION plfoobar_handler() RETURNS OPAQUE AS  '/usr/local/pgsql/lib/plfoobar.so' LANGUAGE 'C'; 
  3. Define a handler that routes a language request for this object to the previously created function. For instance:

     CREATE TRUSTED PROCEDURAL LANGUAGE 'plfoobar'  HANDLER plfoobar_handler  LANCOMPILER 'PL/FooBar'; 

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 Functions

Compiled 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

PostgreSQL Data Type

C Data Type

C Header File

abstime

AbsoluteTime

utils /nabstime.h

bool

bool

include/c.h

box

(BOX *)

utils/geo-decls.h

bytea

(bytea *)

include/postgres.h

char

char

N/A

cid

CID

include/postgres.h

datetime

(DateTime *)

include/c.h or include/postgres.h

float4

(float4 *)

include/c.h or include/postgres.h

float8

(float8 *)

include/c.h or include/postgres.h

int2

int2 or int16

include/postgres.h

int2vector

(int2vector *)

include/postgres.h

int4

int4 or int32

include/postgres.h

lseg

(LSEG *)

include/geo-decls.h

name

(Name)

include/postgres.h

oid

oid

include/postgres.h

oidvector

(oidvector *)

include/postgres.h

path

(PATH *)

utils/geo-decls.h

point

(POINT *)

utils/geo-decls.h

regproc

regproc or REGPROC

include/postgres.h

reltime

RelativeTime

utils/nabstime.h

text

(text *)

include/postgres.h

tid

ItemPointer

storage/itemptr.h

timespan

(TimeSpan *)

include/c.h or include/postgres.h

tinterval

TimeInterval

utils/nabstime.h

xid

(XID *)

include/postgres.h

Data is passed to the compiled function internally in one of three ways:

  • pass- by-value

  • pass-by-reference (fixed length)

  • pass-by-reference (variable length)

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 Function

Two separate conventions exist regarding how C-based functions are to be interfaced :

  • Version-0. This method is the original, but it has now been deprecated. Although this method was fairly simple to use, functions using this method encountered portability problems when trying to port functions across architectures.

  • Version-1. This is the newest interface convention. It overcomes many of the shortfalls of Version-0 calling. It achieves this by relying on macros to encapsulate the passing of arguments, thereby making the resultant code much more portable.

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 Tricks

The 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):

  • Include files are installed under /usr/local/pgsql/include or equivalent.

  • Use the Postgres routines palloc and pfree instead of the standard C functions malloc and free when allocating memory. Memory reserved with palloc will automatically be freed for each transaction, thus preventing memory leaks.

  • Always zero the bytes of your structures using memset or bzero . Even if you initialize all fields of your structure, there might be several bytes of alignment padding (holes in the structure) that contain garbage values.

  • Usually, programs will always require at least postgres.h and fmgr.h to be included. The internal Postgres types are declared in postgres.h , and function manager interfaces ( PG_FUNCTION_ARGS and so on) are in fmgr.h . For portability reasons, it's best to include postgres.h first before any other system or user header files.

  • Symbol names defined within object files must not conflict with each other or with symbols defined in the PostgreSQL server executable. You will have to rename your functions or variables if you get error messages to this effect.

I l @ ve RuBoard


PostgreSQL Essential Reference
PostgreSQL Essential Reference
ISBN: 0735711216
EAN: 2147483647
Year: 2001
Pages: 118
Authors: Barry Stinson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net