PL/pgSQL Tutorial

Introduction

PL/pgSQL (Procedural Language/PostgreSQL) is a loadable procedural programming language supported by the PostgreSQL. PL/pgSQL, as a fully featured programming language, allows much more procedural control than SQL, including the ability to use loops and other control structures. Functions created in the PL/pgSQL language can be called from an SQL statement, or as the action that a trigger performs.

Version: 9.3

Following lists show the features of PL/pgSQL:

• It is easy to use.
• Can be used to create functions and trigger procedures.
• Adds control structures to the SQL language.
• Can perform complex computations.
• Inherits all user-defined types, functions, and operators.
• Can be defined to be trusted by the server.

Advantages of Using PL/pgSQL

PostgreSQL and most other relational databases use SQL as a query language. In database server, every SQL statement executes individually, therefore after sending a query wait for it to be processed, receive and process the result, then send further queries to the server. With PL/pgSQL you can group a block of computation and a series of queries inside the database server, thus having the power of a procedural language and the ease of use of SQL, but with considerable savings of client/server communication overhead.

• Extra round trips between client and server are eliminated.
• Intermediate results that the client does not need do not have to be marshaled or transferred between server and client.
• Multiple rounds of query parsing can be avoided.

PL/pgSQL: Supported argument and result data types

• Functions written in PL/pgSQL can accept as arguments any scalar or array data type supported by the server, and return the same data type result.
• Also, accept or return any composite type (row type) specified by name.
• PL/pgSQL functions can be declared to accept a variable number of arguments by using the VARIADIC marker.
• PL/pgSQL functions can also be declared to accept and return the polymorphic types any element, any array.
• PL/pgSQL functions can also be declared to return a "set" (or table) of any data type that can be returned as a single instance.
• A PL/pgSQL function can be declared to return void if it has no useful return value.

Structure of PL/pgSQL

PL/pgSQL is a block-structured language and each statement within a block is terminated by a semicolon. A block that appears within another block must have a semicolon after END, however, the final END that concludes a function body does not require a semicolon. All keywords are case-insensitive and identifiers are implicitly converted to lower case unless double-quoted, just as they are in ordinary SQL commands.See the following syntax:

Syntax:

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

A label (optional) is only needed if you want to identify the block for use in an EXIT statement, or to qualify the names of the variables declared in the block. If a label is given after END, it must match the label at the block's beginning. Comments work the same way in PL/pgSQL code as in ordinary SQL.

Next:

• Declaration
• Basic Statements
• Control Structures

Previous: TRIGGERS
Next: Declaration