Chapter 9. Embedded Languages

Almost all sophisticated database systems support at least one embedded language. PL/SQL, for instance, is a block-oriented language supported by Oracle databases. In PostgreSQL a set of languages can be used for writing functions you can use in SQL:

• SQL Pure SQL code can be used to write functions.

• C Shared objects can be written and every function in the shared object can be added to PostgreSQL as long as some basic rules are fulfilled.

• PL/pgSQL PL/pgSQL is similar to PL/SQL and it is the standard way of writing functions.

• PL/Perl Perl can be used as an embedded language. In PostgreSQL 7.2, PL/Perl has been improved significantly.

• PL/Tcl Tcl has been designed to be an embedded language and can easily be used in combination with PostgreSQL. Especially for writing triggers, PL/Tcl is a good choice.

PostgreSQL provides an easy interface for adding your own languages. Just a few interfaces have to be implemented and you can use almost any programming language inside PostgreSQL. However, it is only recommended for languages based on C because interacting with libraries or languages that are not written in C is very difficult.

In this chapter we will focus on PL/pgSQL because other languages are beyond the scope of this book.

To implement additional functions for your database, embedded languages are essential, and in most cases it is easy to write extensions for the database. Depending on the kind of functions you want to write, you must choose the best language for your purpose.

When working with PostgreSQL, many people rely on PL/pgSQL. PL/pgSQL is a powerful language that is similar to PL/SQL. It is block-oriented and can only be used from inside PostgreSQL. In this section you will take a closer look at this powerful component of PostgreSQL and you will see how to use PL/pgSQL efficiently.