Why PL/pgSQL?
PL/pgSQL is a powerful SQL scripting language, that is heavily influenced by PL/SQL, the stored procedure language distributed with Oracle. It is included in the vast majority of PostgreSQL installations as a standard part of the product, so it usually requires no setup at all to begin.
PL/pgSQL also has a dirty little secret. The PostgreSQL developers don't want you to know that it is a full-fledged SQL development language, capable of doing pretty much anything within the PostgreSQL database.
Why is this a secret? For years, PostgreSQL did not claim to have stored procedures. PL/pgSQL functions were originally designed to return scalar values and were intended for simple mathematical tasks and trivial string manipulations.
Over the years, PL/pgSQL developed a rich set of control structures and gained the ability to be used by triggers, operators, and indexes. In the end, developers were grudgingly forced to admit that they had a complete, stored procedure development system on their hands.
Along the way, the goal of PL/pgSQL changed from simple scalar functions, to providing access to all of the PostgreSQL system internals, with full control structure. The full list of what is available in the current version is provided at http://www.postgresql.org/docs/current/static/plpgsql-overview.html.
Today, the following are some of the benefits of using PL/pgSQL:
- It is easy to use
- It is available by default on most deployments of PostgreSQL
- It is optimized for the performance of data-intensive tasks
In addition to PL/pgSQL, PostgreSQL also allows many other languages such as PL/Perl, PL/Python, PL/Proxy, and PL/Tcl to be plugged in to the database, some of which will be covered in this book. You may also choose to write your functions in Perl, Python, PHP, bash, and a host of other languages, but they will likely need to be added to your instance of PostgreSQL.