Interacting With a Table

We will start with a table called names, which contains 35 fictional people with unspecified occupations. Each person’s name is unique in the table, and each person has a unique id. The first six rows of this table are shown in Figure 4, to give an idea of the structure of the table.

To start, let’s suppose Megan works as a banker. We could issue a simple UPDATE command to the database to change the occupation field in the table for her, but we’ll write a quick PL/Perl function to illustrate how to send a command to the database. The creation, execution, and verification of this function is shown in Figure 5.

In the set_megan_occupation() function, we create a simple SQL statement and then use spi_exec_query() to run the statement in the database. The result set returned by SELECT set_megan_occupation() contains one row with a NULL value, which happens because set_megan_occupation() RETURNS VOID.

The spi_exec_query() function is part of a group of functions that are used to interact with the database from within PL/Perl. This group of functions is called the Server Programming Interface, or SPI. The DBD::PgSPI Perl module can also be used to access PostgreSQL commands and their results from Perl, but its functionality is experimental at the time of this writing, November, 2007. The most common PL/Perl SPI functions are summarized in Table 1.

Table 1. PL/Perl SPI functions

Suppose each person in the table whose name begins with the letter E is employed as an accountant. Three people in the table have names beginning with the letter E: Erin, Edward, and Elizabeth. Their id values are 10, 33, and 35, respectively. We could write a simple UPDATE command in SQL to reflect their occupations, but again, let’s do it in PL/Perl, instead.

Figure 6 shows a function that finds all the people whose names match a certain regular expression and then sets their occupation. Both the regular expression and the occupation are passed into the function as parameters.