Description
Use the CREATE LANGUAGE command to load a new procedural language into the connected database. This command can be used with languages that you specified using --with-langname when you first installed PostgreSQL, or one you have created yourself. For instance, to correctly add the pltcl language into PostgreSQL, you should have used the tag --with-tcl when you configured PostgreSQL to make its call handler available.
After this command has been issued, you should be able to define functions using the newly added language. Note that the user who executes the CREATE LANGUAGE command must be a superuser.
As of 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 procedural language, you will need to drop it from the database with the DROP LANGUAGE command and recreate it.
Note: If you use CREATE LANGUAGE to create a language in the template1 database, all subsequent databases that are created from the template1 (the default template) will support that language.
In order for a procedural language to be used by PostgreSQL, a call handler must be written for it. That call handler must be compiled into a binary form; it is therefore required that the language used to write a handler be one that compiles into a binary format, such as C or C++.
The call handler must be created within PostgreSQL as a function that does not accept arguments and has a return type of opaque. By defining the handler function in this manner, you enable PostgreSQL to prevent the function (and thus, the language) from ever being used in an arbitrary SQL statement.
Examples
A handler must already exist for the language in question when you use the CREATE LANGUAGE command. The first step in registering a procedural language to create a function that specifies the location of object code for the call handler. The following example creates an example call handler, whose object code is located at /usr/local/pgsql/lib/libexample.so:
booktown=# CREATE FUNCTION example_call_handler () RETURNS opaque
booktown-# AS '/usr/local/pgsql/lib/libexample.so'
booktown-# LANGUAGE 'C';
CREATE
Note: You do not need to define the call handler function if it has already been created 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 the connected database. The following example creates a language called plexample, which uses the call handler created in the preceding example:
booktown=# CREATE PROCEDURAL LANGUAGE 'plexample'
booktown-# HANDLER example_call_handler
booktown-# LANCOMPILER 'My Example';
CREATE