I have been disappointed for a long time with the way PL/Perl handles array arguments. For example, let's consider a simple Perl function that takes a value and a list and checks whether the value is present in the list.

CREATE FUNCTION check_values
{
    my $val = shift;
    my $aref = shift;
	
    foreach (@$aref) {
        return true if $val eq $_ 
    }
    return false;
}

A practical use for this function would be a CHECK constraint ensuring that only a limited set of value can be assigned to a particular column (of course, in real life one can as well use enums).

Even when the function itself is very simple there's no way to translate it directly into the PL/Perl code for PostgreSQL 9.0 and below. The main obstacle to such translation is the conversion of input arguments to strings in PL/Perl functions. This means that an input array of '{1,2,3,4}', passed into a PL/Perl function, will be accessible as a string literal '{1,2,3,4}' and not as an array of 4 elements.

In the past, one way to handle that was writing Perl code to parse the array represented as a string:

CREATE OR REPLACE FUNCTION check_values(TEXT, TEXT[]) RETURNS BOOLEAN AS
$fn$
    my $val = shift;
    my $list = shift;
    $list =~ s/^{(.*)}$/\1/;
    foreach (split /,/, $list) {
        return true if $_ eq $val;
    }
    return false;	
$fn$ LANGUAGE plperl;

SELECT check_values('a', '{a,b,c,d}');
check_values 
--------------
 t
(1 row)

While this code works for simple cases, it fails for more complex ones, such as when the array argument has more than one dimension, or one of the elements contains a comma:

SELECT check_values('a,b,c', ARRAY['a,b,c','e,f,g']);
 check_values 
--------------
 f
(1 row)

I thought that PL/Perl should be smarter on handling the input arguments, and the changes I was working on, which have been submitted as a patch for the current commitfest, teach PL/Perl to handle input arrays properly. Here's how the check function would look with this patch applied.

CREATE OR REPLACE FUNCTION check_values (TEXT, TEXT[]) RETURNS BOOLEAN AS 
$fn$
    my $val = shift;
    my $aref = shift;
	
    foreach (@$aref) {
        return true if $val eq $_;
    }
    return false;
$fn$ LANGUAGE plperl; 

SELECT check_values('a,b,c', ARRAY['a,b,c','e,f,g']);
 check_values 
--------------
 t
(1 row)

Alex Hunsaker did a great review by not only providing valuable comments and bug fixes, but also by extending the patch to generate both a reference and a string representation of array arguments, depending on whether or not they are used in a string context, so the changes won't break existing PL/Perl code. Additionally, he enabled SPI functions in PL/Perl to receive composite type and array arguments directly as Perl hash or array references, without converting them to a string first:

CREATE TYPE foo AS (bar int, baz int);
CREATE TYPE
DO $$
    my $x = {bar => 9, baz => 1}; # composite type represented as a hash ref
    my $plan = spi_prepare('SELECT $1 as foo','foo'); 
    my $rv = spi_exec_prepared($plan, {}, $x); 
    elog(NOTICE, $rv->{rows}->[0]->{foo}->{bar}); 
$$ LANGUAGE plperl;

NOTICE:  9
CONTEXT:  PL/Perl anonymous code block
DO

I look forward for this functionality to be added to 9.1 and to work on other useful features for PL/Perl and PostgreSQL.