Database Reference
In-Depth Information
Suppose we have to install PL/pgSQL inside the
warehouse_db
database. Then, you
will execute the following command (the Linux shell is used in this example):
$ createlang plpgsql warehouse_db
The preceding command assumes that the operating system username is the same as
that of database
superuser
. If this is not the case, give the database superuser name
at the end using the
-U
switch as follows:
$ createlang plpgsql warehouse_db -U postgres
You don't need to insert a semicolon at end, as it's not an SQL
command but rather an invocation via the command-line utility to
the
createlang
shell script that loads the procedural language in
the database.
Understanding the structure of PL/pgSQL
PL/pgSQL is a block-structured and case-insensitive language. A block comprises
statements inside the same set of the
DECLARE
/
BEGIN
and
END
statements.
A block can be deined as follows:
DECLARE
declarations
BEGIN
statements
END;
We will jump to writing functions from here on, where these blocks play an integral
part. Let's start decomposing the structure of function and then understand each
element one by one.
The
CREATE FUNCTION
command is used to create the functions by irst naming the
function and deining its argument and return type, followed by the
declarations
section. Its syntax is as follows:
CREATE OR REPLACE FUNCTION function_name (arguments)
RETURNS type AS
Remember that
CREATE OR REPLACE FUNCTION
is the best
practice to follow than the simple
CREATE FUNCTION
command.
The irst one drops the function with same name if it exists and then
recreates your function.
