In many cases, you don't just want to execute some SQL and forget about it. In most cases, a procedure will loop over the result and do something with it. The following example will show how you can loop over the output of a query. On top of that, I decided to beef up the example a bit and make the function return a composite data type. Working with composite types in Perl is very easy because you can simply stuff the data into a hash and return it.

The return_next function will gradually build up the result set until the function is terminated with a simple return statement.

The example in this listing generates a table consisting of random values:

CREATE TYPE random_type AS  (a float8, b float8); 
 
CREATE OR REPLACE FUNCTION spi_srf_perl(int) 
  RETURNS setof random_type AS 
$$ 
my $rv = spi_query("SELECT random() AS a, 
                           random() AS b 
                      FROM generate_series(1, $_[0])"); 
while (defined (my $row = spi_fetchrow($rv))) 
{ 
  elog(NOTICE, "data: " . 
       $row->{a} . " / " . $row->{b}); 
  return_next({a => $row->{a}, 
               b => $row->{b}}); 
}

return; 
$$ LANGUAGE 'plperl'; 

CREATE FUNCTION 

First, the spi_query function is executed and a loop using the spi_fetchrow function is started. Inside the loop, the composite type will be assembled and stuffed into the result set.

As expected, the function will return a set of random values:

test=# SELECT * FROM  spi_srf_perl(3); 
NOTICE:  data:  0.154673356097192 / 0.278830723837018 
CONTEXT:  PL/Perl function "spi_srf_perl" 
NOTICE:  data:  0.615888888947666 / 0.632620786316693 
CONTEXT:  PL/Perl function "spi_srf_perl" 
NOTICE:  data:  0.910436692181975 / 0.753427186980844 
CONTEXT:  PL/Perl function "spi_srf_perl"  
 a_col              |   b_col 
--------------------+------------------- 
 0.154673356097192  | 0.278830723837018 
 0.615888888947666  | 0.632620786316693 
 0.910436692181975  | 0.753427186980844
(3 rows) 

Keep in mind that set returning functions have to be materialized so that the entire result set will be stored in-memory.