CREATE LANGUAGE — define a new procedural language
CREATE [ OR REPLACE ] [ PROCEDURAL ] LANGUAGE
nameCREATE [ OR REPLACE ] [ TRUSTED ] [ PROCEDURAL ] LANGUAGE
inline_handler] [ VALIDATOR
CREATE LANGUAGE registers a new
procedural language with a PostgreSQL database. Subsequently,
functions and procedures can be defined in this new
As of PostgreSQL 9.1,
most procedural languages have been made into “extensions”, and
should therefore be installed with CREATE
LANGUAGE. Direct use of
LANGUAGE should now be confined to extension
installation scripts. If you have a “bare” language in
your database, perhaps as a result of an upgrade, you can
convert it to an extension using
CREATE LANGUAGE effectively
associates the language name with handler function(s) that are
responsible for executing functions written in the language.
Refer to Chapter 56
for more information about language handlers.
There are two forms of the
LANGUAGE command. In the first form, the user supplies
just the name of the desired language, and the PostgreSQL server consults the
pg_pltemplate system catalog to
determine the correct parameters. In the second form, the user
supplies the language parameters along with the language name.
The second form can be used to create a language that is not
this approach is considered obsolescent.
When the server finds an entry in the
pg_pltemplate catalog for the given
language name, it will use the catalog data even if the command
includes language parameters. This behavior simplifies loading
of old dump files, which are likely to contain out-of-date
information about language support functions.
Ordinarily, the user must have the PostgreSQL superuser privilege to register
a new language. However, the owner of a database can register a
new language within that database if the language is listed in
pg_pltemplate catalog and
is marked as allowed to be created by database owners
tmpldbacreate is true). The
default is that trusted languages can be created by database
owners, but this can be adjusted by superusers by modifying the
creator of a language becomes its owner and can later drop it,
rename it, or assign it to a new owner.
CREATE OR REPLACE LANGUAGE will
either create a new language, or replace an existing
definition. If the language already exists, its parameters are
updated according to the values specified or taken from
pg_pltemplate, but the
language's ownership and permissions settings do not change,
and any existing functions written in the language are assumed
to still be valid. In addition to the normal privilege
requirements for creating a language, the user must be
superuser or owner of the existing language. The
REPLACE case is mainly meant to be used to
ensure that the language exists. If the language has a
pg_pltemplate entry then
REPLACE will not actually change
anything about an existing definition, except in the unusual
case where the
entry has been modified since the language was created.
TRUSTED specifies that
the language does not grant access to data that the user
would not otherwise have. If this key word is omitted
when registering the language, only users with the
privilege can use this language to create new
This is a noise word.
The name of the new procedural language. The name must be unique among the languages in the database.
For backward compatibility, the name can be enclosed by single quotes.
is the name of a previously registered function that will
be called to execute the procedural language's functions.
The call handler for a procedural language must be
written in a compiled language such as C with version 1
call convention and registered with PostgreSQL as a function taking no
arguments and returning the
language_handler type, a placeholder type
that is simply used to identify the function as a call
inline_handler is the
name of a previously registered function that will be
called to execute an anonymous code block (DO command) in this language.
is specified, the language does not support anonymous
code blocks. The handler function must take one argument
internal, which will be
DO command's internal
representation, and it will typically return
void. The return value of the handler is
is the name of a previously registered function that will
be called when a new function in the language is created,
to validate the new function. If no validator function is
specified, then a new function will not be checked when
it is created. The validator function must take one
argument of type
will be the OID of the to-be-created function, and will
A validator function would typically inspect the
function body for syntactical correctness, but it can
also look at other properties of the function, for
example if the language cannot handle certain argument
types. To signal an error, the validator function should
The return value of the function is ignored.
TRUSTED option and the
support function name(s) are ignored if the server has an entry
for the specified language name in
Use DROP LANGUAGE to drop procedural languages.
The system catalog
pg_language (see Section 52.29) records
information about the currently installed languages. Also, the
\dL lists the installed languages.
To create functions in a procedural language, a user must
USAGE privilege for the
language. By default,
PUBLIC (i.e., everyone)
for trusted languages. This can be revoked if desired.
Procedural languages are local to individual databases.
However, a language can be installed into the
template1 database, which will cause it to be
available automatically in all subsequently-created
The call handler function, the inline handler function (if
any), and the validator function (if any) must already exist if
the server does not have an entry for the language in
pg_pltemplate. But when there
is an entry, the functions need not already exist; they will be
automatically defined if not present in the database. (This
might result in
failing, if the shared library that implements the language is
not available in the installation.)
In PostgreSQL versions
before 7.3, it was necessary to declare handler functions as
returning the placeholder type
opaque, rather than
language_handler. To support loading of old dump
CREATE LANGUAGE will accept
a function declared as returning
opaque, but it will issue a notice and change the
function's declared return type to
The preferred way of creating any of the standard procedural languages is just:
CREATE LANGUAGE plperl;
For a language not known in the
pg_pltemplate catalog, a sequence such as
this is needed:
CREATE FUNCTION plsample_call_handler() RETURNS language_handler AS '$libdir/plsample' LANGUAGE C; CREATE LANGUAGE plsample HANDLER plsample_call_handler;
CREATE LANGUAGE is a
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.