GNU/Linux man pages
Livre :
Expressions régulières,
Syntaxe et mise en oeuvre :
GNU/Linux |
RedHat 5.2(Apollo) |
|
 |
create_function(l) |
 |
create function - define a new function
create function function_name
|
([type1 {, type-n}]) | ||
|
returns type-r | ||
|
as {’/full/path/to/objectfile’ | ’sql-queries’} | ||
|
language {’c’ ’sql’ ’internal’ ’plname’} |
With this command, a Postgres user can register a function with Postgres. Subsequently, this user is treated as the owner of the function.
When defining a function with arguments, the input data types, type-1, type-2, ..., type-n, and the return data type, type-r must be specified, along with the language, which may be “c” or “sql”. or “internal”. or “plname”. (The plname is the language name of a created procedural language. See create language(l) for details.) (The arg is clause may be left out if the function has no arguments, or alternatively the argument list may be left empty.) The input types may be base or complex types, or opaque. Opaque indicates that the function accepts arguments of an invalid type such as (char *). The output type may be specified as a base type, complex type, setof <type>, or opaque. The setof modifier indicates that the function will return a set of items, rather than a single item. The as clause of the command is treated differently for C and SQL functions, as explained below.
Functions written in C can be defined to Postgres, which will dynamically load them into its address space. The loading happens either using load(l) or automatically the first time the function is necessary for execution. Repeated execution of a function will cause negligible additional overhead, as the function will remain in a main memory cache.
Internal functions are functions written in C which have been statically linked into the postgres backend process. The as clause must still be specified when defining an internal function but the contents are ignored.
The body of a C function following as should be the FULL PATH of the object code (.o file) for the function, bracketed by quotation marks. (Postgres will not compile a function automatically - it must be compiled before it is used in a define function command.)
C functions
with base type arguments can be written in a straightforward
fashion. The C equivalents of built-in Postgres types are
accessible in a C file if
.../src/backend/utils/builtins.h
is included as a header file. This can be achieved by having
#include <utils/builtins.h>
at the top of the C source file and by compiling all C files
with the following include options:
-I.../src/backend
-I.../src/backend/port/<portname>
-I.../src/backend/obj
before any “.c” programs in the cc
command line, e.g.:
cc -I.../src/backend \
-I.../src/backend/port/<portname> \
-I.../src/backend/obj \
-c progname.c
where “...” is the path to the installed
Postgres source tree and “<portname>” is
the name of the port for which the source tree has been
built.
The convention for passing arguments to and from the user’s C functions is to use pass-by-value for data types that are 32 bits (4 bytes) or smaller, and pass-by-reference for data types that require more than 32 bits.
Complex
arguments to C functions are passed into the C function as a
special C type, TUPLE, defined in
.../src/libpq/libpq-fe.h.
Given a variable t of this type, the C function may
extract attributes from the function using the function
call:
GetAttributeByName(t, "fieldname", &isnull)
where isnull is a pointer to a bool, which the
function sets to true if the field is null. The
result of this function should be cast appropriately as
shown in the examples below.
Different operating systems require different procedures for compiling C source files so that Postgres can load them dynamically. This section discusses the required compiler and loader options on each system.
Under Linux ELF, object files can be generated by specifing the compiler flag -fpic.
Under Ultrix, all object files that Postgres is expected to load dynamically must be compiled using /bin/cc with the “-G 0” option turned on. The object file name in the as clause should end in “.o”.
Under HP-UX, DEC OSF/1, AIX and SunOS 4, all object files must be turned into shared libraries using the operating system’s native object file loader, ld(1).
Under HP-UX, an
object file must be compiled using the native HP-UX C
compiler, /bin/cc, with both the “+z” and
“+u” flags turned on. The first flag turns the
object file into “position-independent code”
(PIC); the second flag removes some alignment restrictions
that the PA-RISC architecture normally enforces. The object
file must then be turned into a shared library using the
HP-UX loader, /bin/ld. The command lines to compile a
C source file, “foo.c”, look like:
cc <other flags> +z +u -c foo.c
ld <other flags> -b -o foo.sl foo.o
The object file name in the as clause should end in
“.sl”.
An extra step
is required under versions of HP-UX prior to 9.00. If the
Postgres header file
include/c.h
is not included in the source file, then the following line
must also be added at the top of every source file:
#pragma HP_ALIGN HPUX_NATURAL_S500
However, this line must not appear in programs compiled
under HP-UX 9.00 or later.
Under DEC
OSF/1, an object file must be compiled and then turned into
a shared library using the OSF/1 loader, /bin/ld. In
this case, the command lines look like:
cc <other flags> -c foo.c
ld <other flags> -shared -expect_unresolved
’*’ -o foo.so foo.o
The object file name in the as clause should end in
“.so”.
Under SunOS 4,
an object file must be compiled and then turned into a
shared library using the SunOS 4 loader, /bin/ld. The
command lines look like:
cc <other flags> -PIC -c foo.c
ld <other flags> -dc -dp -Bdynamic -o foo.so foo.o
The object file name in the as clause should end in
“.so”.
Under AIX,
object files are compiled normally but building the shared
library requires a couple of steps. First, create the object
file:
cc <other flags> -c foo.c
You must then create a symbol “exports” file for
the object file:
mkldexport foo.o ’pwd’ > foo.exp
Finally, you can create the shared library:
ld <other flags> -H512 -T512 -o foo.so -e _nostart \
-bI:.../lib/postgres.exp -bE:foo.exp foo.o \
-lm -lc 2>/dev/null
You should look at the Postgres User’s Manual for an
explanation of this procedure.
SQL functions execute an arbitrary list of SQL queries, returning the results of the last query in the list. SQL functions in general return sets. If their returntype is not specified as a setof, then an arbitrary element of the last query’s result will be returned.
The body of a SQL function following as should be a list of queries separated by whitespace characters and bracketed within quotation marks. Note that quotation marks used in the queries must be escaped, by preceding them with two backslashes (i.e. \’).
Arguments to the SQL function may be referenced in the queries using a $n syntax: $1 refers to the first argument, $2 to the second, and so on. If an argument is complex, then a “dot” notation may be used to access attributes of the argument (e.g. “$1.emp”), or to invoke functions via a nested-dot syntax.
Procedural languages aren’t builtin to Postgres. They are offered by loadable modules. Please refer to the documentation for the PL in question for details about the syntax and how the as clause is interpreted by the PL handler.
The following
command defines a C function, overpaid, of two basetype
arguments.
create function overpaid (float8, int4) returns bool
|
as ’/usr/postgres/src/adt/overpaid.o’ | |
|
language ’c’ |
The C file
"overpaid.c" might look something like:
#include <utils/builtins.h>
bool
overpaid(salary, age)
float8 *salary;
int4 age;
{
if (*salary > 200000.00)
return(TRUE);
if ((age < 30) & (*salary > 100000.00))
return(TRUE);
return(FALSE);
}
The overpaid function can be used in a query, e.g:
select name from EMP where overpaid(salary, age)
One can also write this as a function of a single argument
of type EMP:
create function overpaid_2 (EMP)
|
returns bool |
||||
|
as ’/usr/postgres/src/adt/overpaid_2.o’ |
||||
|
language ’c’ |
The following query is now
accepted:
select name from EMP where overpaid_2(EMP)
In this case, in the body of the overpaid_2 function, the
fields in the EMP record must be extracted. The C file
"overpaid_2.c" might look something like:
#include <utils/builtins.h>
#include <libpq-fe.h>
bool
overpaid_2(t)
TUPLE t;
{
float8 *salary;
int4 age;
bool salnull, agenull;
salary =
(float8 *)GetAttributeByName(t, "salary",
&salnull);
age = (int4)GetAttributeByName(t, "age",
&agenull);
if (!salnull && *salary > 200000.00)
return(TRUE);
if (!agenull && (age<30) && (*salary >
100000.00))
return(TRUE);
return(FALSE)
}
To illustrate a
simple SQL function, consider the following, which might be
used to debit a bank account:
create function TP1 (int4, float8) returns int4
|
as ’update BANK set balance = BANK.balance - $2 |
|||
|
where BANK.acctountno = $1 | |||
|
select(x = 1)’ |
|||
|
language ’sql’ |
A user could execute this
function to debit account 17 by $100.00 as follows:
select (x = TP1( 17,100.0))
The following more interesting examples take a single
argument of type EMP, and retrieve multiple results:
select function hobbies (EMP) returns set of HOBBIES
|
as ’select (HOBBIES.all) from HOBBIES |
|||
|
where $1.name = HOBBIES.person’ | |||
|
language ’sql’ |
information(1), load(l), drop function(l), create language(l).
More than one function may be defined with the same name, as long as the arguments they take are different. In other words, function names can be overloaded. A function may also have the same name as an attribute. In the case that there is an ambiguity between a function on a complex type and an attribute of the complex type, the attribute will always be used.
The name of the C function must be a legal C function name, and the name of the function in C code must be exactly the same as the name used in create function. There is a subtle implication of this restriction: while the dynamic loading routines in most operating systems are more than happy to allow you to load any number of shared libraries that contain conflicting (identically-named) function names, they may in fact botch the load in interesting ways. For example, if you define a dynamically-loaded function that happens to have the same name as a function built into Postgres, the DEC OSF/1 dynamic loader causes Postgres to call the function within itself rather than allowing Postgres to call your function. Hence, if you want your function to be used on different architectures, we recommend that you do not overload C function names.
There is a clever trick to get around the problem just described. Since there is no problem overloading SQL functions, you can define a set of C functions with different names and then define a set of identically-named SQL function wrappers that take the appropriate argument types and call the matching C function.
opaque cannot be given as an argument to a SQL function.
C functions cannot return a set of values.
|
create_function(l) | |