CREATE LANGUAGE() SQL Commands CREATE LANGUAGE()NAME
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 unprivileged user no functionality
to bypass access restrictions. If this keyword is omitted when
registering 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 language name is
case insensitive. A procedural language 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 procedures.
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 successfully cre‐
ated.
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 trigger procedures can be
defined in this new language. The user must have the Postgres supe‐
ruser privilege to register a new language.
WRITING PL HANDLERS
Note: In Postgres 7.1 and later, call handlers must adhere to
the "version 1" function manager interface, not the old-style
interface.
The call handler for a procedural language must be written in a com‐
piled language such as C and registered with Postgres 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 Func‐
tionCallInfoData 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 current 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 a longer-
lived context so that it can cache function definition information
across queries.
When a PL function is invoked as a trigger, no explicit arguments are
passed, but the FunctionCallInfoData's context field points at a Trig‐
gerData 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' language, 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 cannot 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"
PG_FUNCTION_INFO_V1(plsample_call_handler);
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 loadable 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 CREATE LANGUAGE
statement in SQL92.
SQL - Language Statements 29 March 2001 CREATE LANGUAGE()