PostgreSQL Stored Procedures

This PostgreSQL stored procedures section shows you step by step how to develop PostgreSQL user-defined functions.

In PostgreSQL, procedural languages such as PL/pgSQL, C, Perl, Python and Tcl are referred to as stored procedures. The stored procedures add many procedural elements e.g., control structures, loop, complex calculation, etc., to extend SQL-standard. It allows you to develop complex functions in PostgreSQL that may not be possible using plain SQL statements.

We are going to focus on PL/pgSQL procedure language which is similar to the Oracle PL/SQL. The reasons of choosing PL/pgSQL are:

• PL/pgSQL is simple and easy to learn.
• PL/pgSQL comes with PostgreSQL by default. The user-defined functions developed in PL/pgSQL can be used like any built-in functions.
• PL/pgSQL has many features that allow you to develop complex user-defined functions.

Let’s get started programming with PL/pgSQL.

• Introduce to PostgreSQL stored procedures – gives you a brief introduction to PostgreSQL stored procedure.
• Develop the first user-defined function – shows you how to develop the first user-defined function in PL/pgSQL using CREATE FUNCTION statement.
• PL/pgSQL function parameters – introduces you to various kinds of function parameters: IN, OUT, INOUT , and VARIADIC.
• PL/pgSQL function overloading – defines multiple functions with the same name but different argument list.
• PL/pgSQL function that returns a table – develops a function that returns a table.
• PL/pgSQL block structure – illustrates the block structure of PL/pgSQL functions. You will learn how to write the anonymous block, and divide a big block into more logical subblocks.
• PL/pgSQL errors and messages – shows you how to report messages and raise errors using RAISE statement. In addition, we will introduce you to the ASSERT statement for inserting debugging checks into PostgreSQL function.
• PL/pgSQL variables – guides you various ways to declare variables in PL/pgSQL.
• PL/pgSQL constants – unlike variables, the values of constants cannot be change once they are initialized. We show you how to use constants to make the code more readable and easier to maintain.
• PL/pgSQL IF statement – introduces you to three forms of IF statement to execute a command based on a certain condition.
• PL/pgSQL CASE statement – guides you two forms of the CASE statements: simple CASE and searched CASE statements.
• PL/pgSQL loop statements – A loop statement executes a bock of statements repeatedly. PostgreSQL provide you with various kinds of loop statements such as LOOP, WHILE, FOR and FOREACH. We will show you two other statements to control the loop: CONTINUE and EXIT.
• PL/pgSQL cursors – shows you an efficient way to process a large number of rows returned by a query using CURSOR variables.
• Trigger procedures using PL/pgSQL – applies PL/pgSQL to define trigger procedures.