Greenplum PL/pgSQL Procedural Language

About Greenplum Database PL/pgSQL

Greenplum Database PL/pgSQL is a loadable procedural language that is installed and
registered by default with Greenplum Database. You can create user-defined functions using
SQL statements, functions, and operators.

With PL/pgSQL you can group a block of computation and a series of SQL queries inside the
database server, thus having the power of a procedural language and the ease of use of SQL.
Also, with PL/pgSQL you can use all the data types, operators and functions of Greenplum
Database SQL.

When using PL/pgSQL functions, function attributes affect how Greenplum Database creates
query plans. You can specify the attribute IMMUTABLE,
STABLE, or VOLATILE as part of the
LANGUAGE clause to classify the type of function, For information about
the creating functions and function attributes, see the CREATE FUNCTION command in the
Greenplum Database Reference Guide.

The PL/pgSQL Language

PL/pgSQL is a block-structured language. The complete text of a function definition must
be a block. A block is defined as:

[ label ]
[ DECLARE
declarations ]
BEGIN
statements
END [ label ];

Each declaration and each statement within a block is terminated by a semicolon (;). A
block that appears within another block must have a semicolon after END,
as shown in the previous block. The END that concludes a function body
does not require a semicolon.

Important: Do not confuse the use of the BEGIN and
END keywords for grouping statements in PL/pgSQL with the database
commands for transaction control. The PL/pgSQL BEGIN and
END keywords are only for grouping; they do not start or end a
transaction. Functions are always executed within a transaction established by an outer
query — they cannot start or commit that transaction, since there would be no context for
them to execute in. However, a PL/pgSQL block that contains an EXCEPTION
clause effectively forms a subtransaction that can be rolled back without affecting the
outer transaction. For more about the EXCEPTION clause, see the post the
Postgres documentation on error trapping at http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING.

All key words and identifiers can be written in mixed upper and lower case. Identifiers
are implicitly converted to lowercase unless enclosed in double-quotes ( " ).

You can add comments in PL/pgSQL in the following ways:

A double dash (--) starts a comment that extends to the end of the line.

A /* starts a block comment that extends to the next occurrence of */.

Block comments cannot be nested, but double dash comments can be enclosed into a
block comment and a double dash can hide the block comment delimiters /* and
*/.

Any statement in the statement section of a block can be a subblock. Subblocks can be
used for logical grouping or to localize variables to a small group of statements.

The variables declared in the declarations section preceding a block are initialized to
their default values every time the block is entered, not only once per function call. For
example declares the variable quantity several times:

Note: The PL/gpSQL statement SELECT INTO is not supported in
the EXECUTE statement.

PL/pgSQL Examples

The following are examples of PL/pgSQL user-defined functions.

Example: Aliases for Function Parameters

Parameters passed to functions are named with the identifiers such asa
$1, $2. Optionally, aliases can be declared for
$n parameter names for increased readability. Either the alias or the
numeric identifier can then be used to refer to the parameter value.

There are two ways to create an alias. The preferred way is to give a name to the
parameter in the CREATE FUNCTION command, for example:

Example: Using the Data Type of a Table Column

When declaring a variable, you can use %TYPE to specify the data type of
a variable or table column. This is the syntax for declaring a variable with the data type
of a table column:

name table.column_name%TYPE;

You can use this to declare variables that will hold database values. For example, if you
have a column named user_id in your users table. To
declare the variable my_userid with the same data type as the
users.user_id column:

my_userid users.user_id%TYPE;

%TYPE is particularly valuable in polymorphic functions, since the data
types needed for internal variables may change from one call to the next. Appropriate
variables can be created by applying %TYPE to the function’s arguments or
result placeholders.

Example: Composite Type Based on a Table Row

The following syntax declares a composite variable based on table row:

name table_name%ROWTYPE;

Such a row variable can hold a whole row of a SELECT or
FOR query result, so long as that query column set matches the declared
type of the variable. The individual fields of the row value are accessed using the usual
dot notation, for example rowvar.column.

Parameters to a function can be composite types (complete table rows). In that case, the
corresponding identifier $n will be a row variable, and fields can be
selected from it, for example $1.user_id.

Only the user-defined columns of a table row are accessible in a row-type variable, not
the OID or other system columns. The fields of the row type inherit the table’s field size
or precision for data types such as char(n).

The next example function uses a row variable composite type. Before creating the
function, create the table that is used by the function with this
command.

Note: The previous function is classified as a VOLATILE function because
function values could change within a single table scan.

The following SELECT command uses the function.

select t1_calc( 'test1' );

Note: The example PL/pgSQL function uses SELECT with the
INTO clause. It is different from the SQL command SELECT
INTO. If you want to create a table from a SELECT result
inside a PL/pgSQL function, use the SQL command CREATE TABLE AS.

References

Also, see the CREATE FUNCTION
command in the Greenplum Database Reference Guide.

For a summary of built-in Greenplum Database functions, see Summary of Built-in Functions in the Greenplum Database Reference
Guide. For information about using Greenplum Database functions see "Querying Data"
in the Greenplum Database Administrator Guide