Once in a while, your Perl procedure has to do 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 SPI interface, which is a C interface 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 you might want to do is simply run SQL and retrieve the number of rows fetched. The spi_exec_query function is here to do exactly that. The first parameter passed to the function is the query. The second parameter has the number of rows you actually want to retrieve. For simplicity reasons, I decided to fetch all of them. The following listing has an example:

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 stored procedure languages provide a means to send log messages. In the case of PL/Perl, this function is called elog and 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)