I had a problem that I needed to solve recently, and the most elegant way to do this was with a stored procedure. However, the procedure needed to return 2 columns – an index id and a sort order. One way to tackle this is to have the plperl function called by a wrapping plpgsql function, but that’s ugly. I wanted to do it all in plperl, but couldn’t find any documentation on it with custom rowtypes. So, off to IRC for some info.

xzilla on #postgresql (irc.freenode.net) pointed me out to a recent blog posting of his that seemed to do what I wanted using Out parameters. This was really great, and I knew I had something I could work with there. Thanks xzilla!

So, I took it a bit further and modified his example slightly to see if I could also do it without the out parameters and just use the rowtype. Here’s the result:

CREATE TYPE footype AS (number int, oddoreven text);

CREATE OR REPLACE FUNCTION odd_or_even(int) RETURNS SETOF footype
AS $$
my @Return;
my $Count = 1;
my $Input = $_[0];

while ($Count <= 10) {
  if ($Input%2 == 0) {
     push @Return, { number => $Input, oddoreven => 'even' }
  } else {
     push @Return, { number => $Input, oddoreven => 'odd' }
  }
 $Count++;
 $Input++;
}

 foreach my $Row (@Return) {
     return_next $Row;
 }
 return;

$$ LANGUAGE plperl;

test=# SELECT * FROM odd_or_even(23)
test=# ORDER BY odd_or_even.oddoreven;
 number | oddoreven
--------+-----------
     24 | even
     26 | even
     28 | even
     30 | even
     32 | even
     23 | odd
     25 | odd
     27 | odd
     29 | odd
     31 | odd
(10 rows)