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
- PL/pgSQL function parameters – introduces you to various kinds of function parameters:
- 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
RAISEstatement. In addition, we will introduce you to the
ASSERTstatement 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
- PL/pgSQL loop statements – A loop statement executes a bock of statements repeatedly. PostgreSQL provide you with various kinds of loop statements such as
FOREACH. We will show you two other statements to control the loop:
- PL/pgSQL cursors – shows you an efficient way to process a large number of rows returned by a query using
- Trigger procedures using PL/pgSQL – applies PL/pgSQL to define trigger procedures.