Database Access from PL/Perl Code

Could someone please assist to rectify the errors in this piece of code.

(this is just a simplified version of my code, but it identifies the issues).

DROP FUNCTION perl_func(VARIADIC params character varying[]);

CREATE OR REPLACE FUNCTION perl_func(VARIADIC params character varying[])
  RETURNS character varying AS
$BODY$   
   $val = spi_query("array_to_string($1,'###');");
   $s = `echo $val`;
   return $s; 
$BODY$
  LANGUAGE plperlu VOLATILE
  COST 100;

SELECT * from perl_func('a','d');

This returns a syntax error:

ERROR:  syntax error at or near "," at line 2.
CONTEXT:  PL/Perl function “perl_func”

The main aim: Is to formulate the input params as a string, and use it to call some command-line program, which returns a String. Then output this string as the return of this function.

Solution

Original attempt:

The variable $1 means something different in Perl than it does in plpgsql functions. Try

CREATE OR REPLACE FUNCTION perl_func(VARIADIC params character varying[])
  RETURNS character varying AS
$BODY$
   use strict;
   use warnings;
   my $val = join("###",@_);
   my $s = `echo $val`;
   chomp($s);
   return $s; 
$BODY$
  LANGUAGE plperlu VOLATILE
  COST 100;

Unfortunately, this doesn't work (as noted below) because it doesn't split the strings properly. Unfortunately, plperl considers the arguments to be a single string:

CREATE OR REPLACE FUNCTION perl_str(VARIADIC text[])
  RETURNS text AS
$BODY$
   use strict;
   use warnings;
   my $array_str=shift;
   return $array_str;
$BODY$
  LANGUAGE plperl VOLATILE;

And when we select it:

> select perl_str(ARRAY['abc','def']);
 perl_str
-----------
 {abc,def}
(1 row)

And to find out for certain that it's a single string, we can turn to our old friend Data::Dumper:

CREATE OR REPLACE FUNCTION perl_dump(VARIADIC text[])
  RETURNS text AS
$BODY$
   use strict;
   use warnings;
   use Data::Dumper;
   my $array_str=shift;
   return Dumper($array_str);
$BODY$
  LANGUAGE plperl VOLATILE;

And this returns:

> select perl_dump(ARRAY['abc','def']);
      perl_dump
----------------------
 $VAR1 = '{abc,def}';

(1 row)

So, the output is considered an actual string with curly braces on the end and the entries separated by commas. Okay, well...this is annoying, but at least we can deal with it:

CREATE OR REPLACE FUNCTION final_perl_func(VARIADIC text[])
  RETURNS text AS
$BODY$
   use strict;
   use warnings;
   my $array_string=shift;
   $array_string=substr($array_string,1,length($array_string)-2);
   my @array=split(/,/,$array_string);
   my $val=join("###",@array);
   my $s=`echo $val`;
   chomp($s);
   return $s;
$BODY$
  LANGUAGE plperl VOLATILE;

And this gets us what we want:

> select final_perl_func(ARRAY['abc','def']);
 final_perl_func
-----------------
 abc###def
(1 row)

Note that, for reasons I don't understand, I had to resort to the use of substr instead of a simple regex replace ($array_string=~s/{}//g;), as the plperl function kept returning the curly brackets when I tried the regex replacement.

I hadn't dealt with plperl a lot before answering your questions about it, and the main thing that I've learned is that it's a major pain...you might want to consider manipulating the database from Perl using the Perl DBI.