Once in a while, your Perl procedure has to carry out some database work. Remember, the function is part of the database connection. Therefore, it is pointless to actually create a database connection. To talk to the database, the PostgreSQL server infrastructure provides the Server Programming Interface (SPI), which is a C interface that you can use to talk to database internals. All procedural languages that help you to run server-side code use this interface to expose functionality to you. PL/Perl does the same, and in this section, you will learn how to use the Perl wrapper around the SPI interface.

The most important thing that you might want to do is simply run SQL, and retrieve the number of rows that have been fetched. The spi_exec_query function is here to do exactly that. The first parameter that's passed to the function is the query parameter. The second parameter has the number of rows that you actually want to retrieve. For simplicity, I decided to fetch all of them. The following code shows an example of this:

test=# CREATE OR REPLACE FUNCTION spi_sample(int)  
RETURNS void  AS 
$$ 
my $rv = spi_exec_query(" SELECT * 
  FROM  generate_series(1, $_[0])", $_[0] 
);  
elog(NOTICE, "rows  fetched: " . $rv->{processed});  
elog(NOTICE, "status: " . $rv->{status}); 
 
return; 
$$ LANGUAGE 'plperl'; 

SPI will execute the query and display the number of rows. The important thing here is that all of the stored procedure languages provide a means to send log messages. In the case of PL/Perl, this function is called elog, and it takes two parameters. The first one defines the importance of the message (INFO, NOTICE, WARNING, ERROR, and so on), and the second parameter contains the actual message.

The following message shows what the query returns:

test=# SELECT spi_sample(9);
NOTICE:  rows  fetched: 9 
NOTICE:  status: SPI_OK_SELECT 
 spi_sample 
------------ 
 
(1 row)