As already stated previously in this chapter, PostgreSQL gives you the power to write functions and stored procedures in various languages. The following options are available and shipped along with the PostgreSQL core:

• SQL
• PL/pgSQL
• PL/Perl and PL/PerlU
• PL/Python
• PL/Tcl and PL/TclU

SQL is the obvious choice to write functions and should be used whenever possible, as it gives the most freedom to the optimizer. However, if you want to write slightly more complex code, PL/pgSQL might be the language of your choice.

PL/pgSQL offers flow control and a lot more. In this chapter, some of the more advanced and less known features of PL/pgSQL will be shown but do keep in mind that this chapter is not meant to be a complete tutorial on PL/pgSQL.

The core contains code to run server side functions in Perl. Basically, the logic is the same here. Code will be passed as a string and executed by Perl. Remember that PostgreSQL does not speak Perl; it merely has the code to pass things on to the external programming language.

Maybe you have noticed that Perl and TCL are available in two flavors: "trusted" (PL/Perl and PL/TCL) and "untrusted" (PL/PerlU and PL/TCLU). The difference between a trusted and an untrusted language is actually an important one. In PostgreSQL, a language is loaded directly into the database connection. Therefore, the language is able to do quite a lot of nasty stuff. To get rid of security problems, the concept of trusted languages has been invented. The idea is that a trusted language is restricted to the very core of the language. It is not possible to do the following:

• Include libraries
• Open network sockets
• Perform system calls of any kind, which would include opening files, and so on

Perl offers something called taint mode, which is used to implement this feature in PostgreSQL. Perl will automatically restrict itself to trusted mode and error out if a security violation is about to happen. In untrusted mode, everything is possible, and therefore, only the superuser is allowed to run untrusted code.

If you want to run trusted as well as untrusted code, you have to activate both languages, plperl and plperlu ( pltcl and pltclu respectively).

Python is currently only available as an untrusted language; therefore, administrators have to be very careful when it comes to security in general, as a function running in untrusted mode can bypass all security mechanisms enforced by PostgreSQL. Just keep in mind that Python is running as part of your database connection and is in no way responsible for security.

Let's get started with the most awaited topic of this chapter.