CREATE LANGUAGE
CREATE LANGUAGE — define a new procedural language
Synopsis
CREATE [ OR REPLACE ] [ PROCEDURAL ] LANGUAGEnameCREATE [ OR REPLACE ] [ TRUSTED ] [ PROCEDURAL ] LANGUAGEnameHANDLERcall_handler[ INLINEinline_handler] [ VALIDATORvalfunction]
Description
CREATE LANGUAGE registers a new procedural language with aPostgreSQL database. Subsequently, functions and procedures can be defined in this new language.
Note
As ofPostgreSQL 9.1, most procedural languages have been made into“extensions”, and should therefore be installed withCREATE EXTENSION notCREATE LANGUAGE. Direct use ofCREATE 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 usingCREATE EXTENSION.langname FROM unpackaged
CREATE LANGUAGE effectively associates the language name with handler function(s) that are responsible for executing functions written in the language. Refer toChapter 55 for more information about language handlers.
There are two forms of theCREATE LANGUAGE command. In the first form, the user supplies just the name of the desired language, and thePostgreSQL server consults thepg_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 defined inpg_pltemplate, but this approach is considered obsolescent.
When the server finds an entry in thepg_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 thePostgreSQL 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 thepg_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 contents ofpg_pltemplate. 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 frompg_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. TheREPLACE case is mainly meant to be used to ensure that the language exists. If the language has apg_pltemplate entry thenREPLACE will not actually change anything about an existing definition, except in the unusual case where thepg_pltemplate entry has been modified since the language was created.
Parameters
TRUSTEDTRUSTEDspecifies 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 thePostgreSQL superuser privilege can use this language to create new functions.PROCEDURALThis is a noise word.
nameThe 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.
HANDLERcall_handlercall_handleris 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 withPostgreSQL as a function taking no arguments and returning thelanguage_handlertype, a placeholder type that is simply used to identify the function as a call handler.INLINEinline_handlerinline_handleris the name of a previously registered function that will be called to execute an anonymous code block (DO command) in this language. If noinline_handlerfunction is specified, the language does not support anonymous code blocks. The handler function must take one argument of typeinternal, which will be theDOcommand's internal representation, and it will typically returnvoid. The return value of the handler is ignored.VALIDATORvalfunctionvalfunctionis 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 typeoid, which will be the OID of the to-be-created function, and will typically returnvoid.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 use the
ereport()function. The return value of the function is ignored.
TheTRUSTED option and the support function name(s) are ignored if the server has an entry for the specified language name inpg_pltemplate.
Notes
UseDROP LANGUAGE to drop procedural languages.
The system catalogpg_language (seeSection 51.29) records information about the currently installed languages. Also, thepsql command\dL lists the installed languages.
To create functions in a procedural language, a user must have theUSAGE privilege for the language. By default,USAGE is granted toPUBLIC (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 thetemplate1 database, which will cause it to be available automatically in all subsequently-created databases.
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 inpg_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 inCREATE LANGUAGE failing, if the shared library that implements the language is not available in the installation.)
InPostgreSQL versions before 7.3, it was necessary to declare handler functions as returning the placeholder typeopaque, rather thanlanguage_handler. To support loading of old dump files,CREATE LANGUAGE will accept a function declared as returningopaque, but it will issue a notice and change the function's declared return type tolanguage_handler.
Examples
The preferred way of creating any of the standard procedural languages is just:
CREATE LANGUAGE plperl;
For a language not known in thepg_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;
Compatibility
CREATE LANGUAGE is aPostgreSQL extension.