PHP Manual

PEAR Manual

Smarty Manual

PostgreSQL

MySQL Manual

Perl Manual

practical postgresql
prevnext

chapter 11. pl/pgsql

table of contents
adding pl/pgsql to your database
language structure
using variables
controlling program flow
pl/pgsql and triggers

pl/pgsql is a loadable, procedural language, similar to the oracle procedural language, pl/sql. a procedural language is a programming language used to specify a sequence of steps that are followed to produce an intended programmatic result.

you can use pl/pgsql to group sequences of sql and programmatic statements together within a database server, reducing network and communications overhead incurred by client applications having to constantly request data from the database and perform logic operations upon that data from a remote location.

you have access to all postgresql data types, operators, and functions within pl/pgsql code. the "sql" in pl/pgsql is indicative of the fact that you are allowed to directly use the sql language from within pl/pgsql code. the use of sql within pl/pgsql code can increase the power, flexibility, and performance of your programs. if multiple sql statements are executed from a pl/pgsql code block, the statements are processed at one time, instead of the normal behavior of processing a single statement at a time.

another important aspect of using pl/pgsql is its portability; its functions are compatible with all platforms that can operate the postgresql database system.

the following sections describe how to make pl/pgsql available as a procedural language in your database.

adding pl/pgsql to your database

programming languages are made available to databases by being created as a database object. you will therefore need to add the pl/pgsql language to your database before you can use it (it is installed with postgresql by default). the following steps demonstrate how to add pl/pgsql to an existing database.

adding pl/pgsql to your database

to add pl/pgsql to your postgresql database, you can either use the createlang application from the command line, or the create language sql command from within a database client such as psql. the use of the create language command first requires the creation of the pl/pgsql call handler, which is the function that actually processes and interprets the pl/pgsql code.

though the createlang utility is simpler to use, as it abstracts the creation of the call handler and the language away from the user, the following sections document both methods.

note: installing pl/pgsql in the template1 database causes all subsequent databases that are created with template1 as their template (which is the default) to also have pl/pgsql installed.

using psql to add pl/pgsql

create language is the sql command which adds procedural languages to the currently connected database. before it can be used, however, the create function command must first be used to create the procedural call handler.

here is the syntax to create a pl/pgsql call handler with create function:

  create function plpgsql_call_handler()          returns opaque as '/postgres_library_path/plpgsql.so' language 'c'

in this syntax, postgres_library_path is the absolute system path to the installed postgresql library files. this path, by default, is /usr/local/pgsql/lib. example 11-1 uses the create function command to create the pl/pgsql call handler, assuming the plpgsql.so file is in the default location.

example 11-1. creating the pl/pgsql call handler

booktown=# create function plpgsql_call_handler () booktown-#                 returns opaque booktown-#                 as '/usr/local/pgsql/lib/plpgsql.so' booktown-#                 language 'c'; create

example 11-1 only creates the function handler; the language itself must also be added with the create language command. here is the syntax to add pl/pgsql to a database:

  create language 'plpgsql' handler plpgsql_call_handler                             lancompiler 'pl/pgsql'

in this syntax, plpgsql is the name of the language to be created, the plpgsql_call_handler is the name of the call handler function (e.g., the one created in example 11-1), and the pl/pgsql string constant following the lancompiler keyword is an arbitrary descriptive note.

example 11-2 adds pl/pgsql to the booktown database with the create language command.

example 11-2. adding pl/pgsql with create language

booktown=# create language 'plpgsql' handler plpgsql_call_handler booktown-#                           lancompiler 'pl/pgsql'; create

the name following the handler keyword should be the same name which is used to create the call handler. since example 11-1 created a call handler named plpgsql_call_handler, example 11-2 uses the same name.

the string following the lancompiler keyword is an outdated legacy clause, and its value is not consequential. even so, as of postgresql 7.1.x, it is a required clause. it is commonly used as a comment space to describe the language.

using createlang to add pl/pgsql

to execute createlang you will first need to be at the command prompt. if the operating system username you are currently logged into is the same as that of a database superuser account on the target database, you can call createlang with the command shown in example 11-3 (you will be asked for a password if the database requires one). otherwise, to pass the username of a database superuser to createlang, use the -u flag as shown in example 11-4.

example 11-3. using createlang as a database superuser

$ cd /usr/local/pgsql/bin booktown=# createlang plpgsql booktown

example 11-4. explicitly passing a superuser account name to createlang

$ cd /usr/local/pgsql/bin/ $ createlang plpgsql -u manager booktown

the createlang program will return you to a shell prompt upon successful execution.

prevhomenext
programming with postgresqluplanguage structure