Chapter 9. PL/pgSQL
PL/pgSQL is a loadable procedural language for the PostgreSQL database system. PL/pgSQL is similar to the Oracle procedural language PL/SQL. The next section on adding PL/pgSQL to the database will walk you through the steps to loading the PL/pgSQL language for a database. PL/pgSQL is a powerful language that greatly increase the flexibility of the PostgreSQL DBMS. Below is a list of potential benefits to using PL/pgSQL.
You can define all C language functions, except those used for input/output. This includes calculated functions for user defined types.
You have access to all of the PostgreSQL, data types, operators, and functions.
You have access to SQL (Structured Query Language). Developers can create complex programs using PL/pgSQL and SQL.
SQL by default allows only the execution of a single statement at a time.You can achieve performance gains due to the processing of multiple statements at one time.
You will also gain large scale portability with the use of PL/pgSQL. This is a result of PL/pgSQL being compatible with all platforms that PostgreSQL supports.
Adding PL/pgSQL to your Database
Before you can use the PL/pgSQL language, you will need to add it to your database. Use these steps to add the PL/pgSQL language to any existing database:
Step1: Log into the database
The first step to getting PL/pgSQL up and running is to log into the PostgreSQL database as a PostgreSQL superuser. For simplicity sake, we are going to use the standard PostgreSQL superuser postgres. As a reminder, below are the command required to login using a Unix/Linux based system to the postgres user.
su - postgres
Step 2: Using createlang to add PL/pgSQL
Adding procedural languages to PostgreSQL is done by using the createlang command.Use the creatlang command. When the creatdb command is used, it utilizes the template1 database to create the database.
 | Remember template1 |
|---|---|
If you use the creatlang command on the template1 database, then every subsequent database created will have the PL/pgSQL language automatically loaded. |
To add PL/pgSQL to the booktown database use the following command:
createlang plpgsql booktownThe createlang program will return you to a shell prompt upon successfull execution.
Step 3: Start programming in PL/pgSQL
You are now ready to log into your database and use the PL/pgSQL language to define functions for PostgreSQL. Please refer to the subsequent sections on ways to define functions, blocks, and variables using PL/pgSQL.