CREATE LANGUAGE - Defines a new language for functions
SYNOPSIS
CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE 'langname'
HANDLER call_handler
LANCOMPILER 'comment'
INPUTS
TRUSTED
TRUSTED specifies that the call handler for the
language is safe; that is, it offers an unprivi
leged user no functionality to bypass access
restrictions. If this keyword is omitted when reg
istering the language, only users with the Postgres
superuser privilege can use this language to create
new functions.
langname
The name of the new procedural language. The lan
guage name is case insensitive. A procedural lan
guage cannot override one of the built-in languages
of Postgres.
HANDLER call_handler
call_handler is the name of a previously registered
function that will be called to execute the PL pro
cedures.
comment
The LANCOMPILER argument is the string that will be
inserted in the LANCOMPILER attribute of the new
pg_language entry. At present, Postgres does not
use this attribute in any way.
OUTPUTS
CREATE This message is returned if the language is suc
cessfully created.
ERROR: PL handler function funcname() doesn't exist
This error is returned if the function funcname()
is not found.
DESCRIPTION
Using CREATE LANGUAGE, a Postgres user can register a new
language with Postgres. Subsequently, functions and trig
ger procedures can be defined in this new language. The
user must have the Postgres superuser privilege to regis
ter a new language.
Note: In Postgres 7.1 and later, call handlers must
adhere to the "version 1" function manager inter
face, not the old-style interface.
The call handler for a procedural language must be written
in a compiled language such as C and registered with Post
gres as a function taking no arguments and returning the
opaque type, a placeholder for unspecified or undefined
types. This prevents the call handler from being called
directly as a function from queries. (However, arguments
may be supplied in the actual call when a PL function in
the language offered by the handler is to be executed.)
The call handler is called in the same way as any other
function: it receives a pointer to a FunctionCallInfoData
struct containing argument values and information about
the called function, and it is expected to return a Datum
result (and possibly set the isnull field of the Function
CallInfoData struct, if it wishes to return an SQL NULL
result). The difference between a call handler and an
ordinary callee function is that the flinfo->fn_oid field
of the FunctionCallInfoData struct will contain the OID of
the PL function to be called, not of the call handler
itself. The call handler must use this field to determine
which function to execute. Also, the passed argument list
has been set up according to the declaration of the target
PL function, not of the call handler.
It's up to the call handler to fetch the pg_proc entry and
to analyze the argument and return types of the called
procedure. The AS clause from the CREATE FUNCTION of the
procedure will be found in the prosrc attribute of the
pg_proc table entry. This may be the source text in the
procedural language itself (like for PL/Tcl), a pathname
to a file, or anything else that tells the call handler
what to do in detail.
Often, the same function is called many times per SQL
statement. A call handler can avoid repeated lookups of
information about the called function by using the
flinfo->fn_extra field. This will initially be NULL, but
can be set by the call handler to point at information
about the PL function. On subsequent calls, if
flinfo->fn_extra is already non-NULL then it can be used
and the information lookup step skipped. The call handler
must be careful that flinfo->fn_extra is made to point at
memory that will live at least until the end of the cur
rent query, since an FmgrInfo data structure could be kept
that long. One way to do this is to allocate the extra
data in the memory context specified by flinfo->fn_mcxt;
such data will normally have the same lifespan as the
FmgrInfo itself. But the handler could also choose to use
nition information across queries.
When a PL function is invoked as a trigger, no explicit
arguments are passed, but the FunctionCallInfoData's con
text field points at a TriggerData node, rather than being
NULL as it is in a plain function call. A PL handler
should provide mechanisms for PL functions to get at the
trigger information.
NOTES
Use CREATE FUNCTION to create a function.
Use DROP LANGUAGE to drop procedural languages.
Refer to the table pg_language for further information:
Table "pg_language"
Attribute | Type | Modifier
---------------+---------+----------
lanname | name |
lanispl | boolean |
lanpltrusted | boolean |
lanplcallfoid | oid |
lancompiler | text |
lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler
-------------+---------+--------------+---------------+-------------
internal | f | f | 0 | n/a
C | f | f | 0 | /bin/cc
sql | f | f | 0 | postgres
The call handler for a procedural language must normally
be written in C and registered as 'internal' or 'C' lan
guage, depending on whether it is linked into the backend
or dynamically loaded. The call handler cannot use the
old-style 'C' function interface.
At present, the definitions for a procedural language can
not be changed once they have been created.
USAGE
This is a template for a PL handler written in C:
#include "executor/spi.h"
#include "commands/trigger.h"
#include "utils/elog.h"
#include "fmgr.h"
#include "access/heapam.h"
#include "utils/syscache.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
Datum
plsample_call_handler(PG_FUNCTION_ARGS)
{
Datum retval;
if (CALLED_AS_TRIGGER(fcinfo))
{
/*
* Called as a trigger procedure
*/
TriggerData *trigdata = (TriggerData *) fcinfo->context;
retval = ...
} else {
/*
* Called as a function
*/
retval = ...
}
return retval;
}
Only a few thousand lines of code have to be added instead
of the dots to complete the PL call handler. See CREATE
FUNCTION for information on how to compile it into a load
able module.
The following commands then register the sample procedural
language:
CREATE FUNCTION plsample_call_handler () RETURNS opaque
AS '/usr/local/pgsql/lib/plsample.so'
LANGUAGE 'C';
CREATE PROCEDURAL LANGUAGE 'plsample'
HANDLER plsample_call_handler
LANCOMPILER 'PL/Sample';
COMPATIBILITY
SQL92
CREATE LANGUAGE is a Postgres extension. There is no CRE
ATE LANGUAGE statement in SQL92.
Man(1) output converted with
man2html