# Changes in PL/Perl

> 
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 …

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](<http://www.postgresql.org/docs/current/static/datatype-enum.html>)).

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](<https://commitfest.postgresql.org/action/patch_view?id=491>) for the [current commitfest](<https://commitfest.postgresql.org/action/commitfest_view?id=9>), 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.

---
[View this page online](https://www.commandprompt.com/blog/changes_in_plperl/)