Name
CREATE LANGUAGE
-- Defines a new language to be used by functions.
Synopsis
CREATE [ TRUSTED ] PROCEDURAL LANGUAGE 'langname'
HANDLER call_handler
LANCOMPILER 'comment'
Parameters
- TRUSTED
The TRUSTED keyword specifies whether or not the
language lets unpriviliged users bypass account and permisson-related
access restrictions. If this parameter is not defined during
creation of the language, only database superusers will be
able to use the language to create new functions.
- langname
This parameter accepts the name of the new procedural language to
define. It is case insensitive.
 | Note |
|---|
| | A procedural language does not and cannot override an existing,
built-in PostgreSQL language.
|
- HANDLER call_handler
The call_handler parameter
holds the name of an already-defined function that will be called to
execute the PL procedures.
- comment
The comment parameter is the
string you wish to be inserted into the LANCOMPILER
attribute of the pg_language entry.
| Non-functional |
|---|
| | This parameter is currently not used by PostgreSQL.
|
Results
- CREATE
This message is displayed if the language is succesfully created.
- ERROR: PL handler function funcname() doesn't exist
You will receive this error if the function you specified
with the funcname
parameter cannot be found.
Description
Use the CREATE LANGUAGE command to
load a new or existing language into a database. This command can be
used with languages that you specified using
--with-langname when you first installed PostgreSQL. For
instance, to correctly add the pltcl language into PostgreSQL, you should
have used the tag --with-tcl when you
configured PostgreSQL.
After this command has been issued you should be able to define functions
and trigger procedures using the newly added language. The user who executes
this command must have the PostgreSQL superuser
privilege.
As of the PostgreSQL 7.1.x (the most current version at the printing of this
book), once a procedural languages is defined, the definition cannot be modified.
To change the definition of your PL, you'll need to drop it from the database
with the DROP LANGUAGE command and recreate
it.
| Using template1 |
|---|
| | If you use CREATE LANGUAGE to create a
language in the template1 database, all
subsequent databases that are created from the template1
(which is the default template) will support that language.
|
PL Handlers
In order for a procedural language to be used by PostgreSQL, a call handler
must be written for it and compiled into binary form (hence it is required
that the language used to write a handler is one that compiles into binary,
such as C or C++). The handler must also be registered within PostgreSQL
as a function that does not accept arguments and has a return type of
opaque. By doing so, PostgreSQL can stop the function
from being used normally by an SQL statement.
| Version Difference |
|---|
| | PostgreSQL versions 7.1.x and later defines the call handler to adhere with
the "version 1" function manager interface, instead of the old-style
interface.
|
Examples
A handler must already exist for the language in question to use the following
commands. The first step of registering a procedural language is creating a
function that specifies the location of the call handler.
CREATE FUNCTION your_call_handler () RETURNS opaque
AS '/usr/local/pgsql/lib/libyourhandler.so'
LANGUAGE 'C';
| Defining a Function |
|---|
| | You do not need to define the call handler function above if
it has already been defined by the programmer. For illustrative purposes,
we are assuming that the programmer has not defined a function that refers
to the call handler.
|
The second step is to use CREATE LANGUAGE
to load the existing language into your database.
CREATE PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';