Databases - Practical PostgreSQL
Previous Page	Home	Next Page

CREATE LANGUAGE

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 indicates that the PostgreSQL lets unprivileged users bypass user and permission-related access restrictions on the language. If this parameter is not specified during creation of the language, only database superusers will be able to use the language to create new functions.
PROCEDURAL: The optional PROCEDURAL noise term. This may be used to increase readability of your CREATE LANGUAGE statements, but has no effect.
langname: The name of the new procedural language to define. This name is case insensitive. A procedural language will not (and cannot) override an existing, built-in PostgreSQL language.
HANDLER call_handler: The name of the already-defined function that will be called to execute the PL procedures.
comment: A string that is inserted into the lancompiler column of the pg_language system table. The LANCOMPILER clause is a legacy clause with no practical effect, and may be removed in a future PostgreSQL release. However, as of version 7.1.x, this is still a required clause.

Results

CREATE: The message returned when a new language is successfully created.
ERROR: PL handler function call_handler() doesn't exist: The error returned if the function you specified as the call handler with call_handler parameter cannot be found.

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

Databases - Practical PostgreSQL
Previous Page	Home	Next Page