CREATE [ TRUSTED ] PROCEDURAL LANGUAGE 'langname' HANDLER call_handler LANCOMPILER 'comment'
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.
This parameter accepts the name of the new procedural language to define. It is case insensitive.
A procedural language does not and cannot override an existing, built-in PostgreSQL language.
The call_handler parameter holds the name of an already-defined function that will be called to execute the PL procedures.
The comment parameter is the string you wish to be inserted into the LANCOMPILER attribute of the pg_language entry.
This parameter is currently not used by PostgreSQL.
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.
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.
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.
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.
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';