Out parameters in plperl

One evening last week, I got some suspcious IM’s from Theo, asking me questions like what is the syntax of an out parameter? (out param datatype), and what return type needs to be used wrt out params (generally setof record). A few minutes later he asked me how one returns the rows from such a function in plperl, to which I sheepishly replied “I think you have to build an array and then return each row that way”, which I thought was close but honestly was more of an educated guess. A guess because I couldn’t remember anyone ever using out params in plperl. And that got me thinking that, maybe plperl didn’t support out params? A quick google search turned up an article on plperl and postgresql 8.1 by Andrew Dunstan, who noted that IN/OUT params for plperl were still on the TODO list. So I did a quick scan of the 8.2 release notes, and found nothing there about IN/OUT support added to pl/perl. One last check, I popped on to #postgresql, and located David Fetter, a noted plperl advocate, and asked him if PostgreSQL supported OUT params for plperl. His reply? “If it does, it’s undocumented”. Drat. As I was on my way home anyway, I swung by Theo’s office and told him that I wasn’t sure if plperl actually supported OUT parameters, to which he replied “Oh no problem, I already have it working”. Never doubt what a person can accomplish when they don’t know they can’t do something. So, for all of that, ISTM a good idea to post a simple example of how it actually works, so that others trying to figure out the syntax might have something to look at. This example is about as dead simple as it gets, we just iterate through some numbers, and spit out two columns for each item.

CREATE OR REPLACE FUNCTION setof_plperl(out number integer, out oddoreven text)
RETURNS SETOF record
AS $$
    my @rv;
    my $val = 1;

    while ($val <= 10)     {         
        if ($val%2 == 0) {             
            push @rv, { number => $val, oddoreven => 'even' }         
        } else {             
            push @rv, { number => $val, oddoreven => 'odd' }         
        }

        $val++;     
    }
     
    foreach my $row (@rv) {         
        return_next $row;
    }

    return;

$$ LANGUAGE plperl;

pagila=# select * from setof_plperl();
 number | oddoreven
--------+-----------
      1 | odd
      2 | even
      3 | odd
      4 | even
      5 | odd
      6 | even
      7 | odd
      8 | even
      9 | odd
     10 | even
(10 rows)



There you go!  Hopefully that basic example will be enough to get you started. On a side note, I spoke with David after this "discovery" and he said that he has investigated things and found a couple ways to do this, and has promised to submit a doc patch for 8.3, so hopefully this will get even easier.