Thread: PLPERLU help

PLPERLU help

From

Atif Jung

Date:

30 June 2010, 09:00:02

I have the following SPL written in plperlu which I need help with please.

CREATE or replace FUNCTION validatesubmission(submission_num text) RETURNS INTEGER AS $$
$ISIS2_USER = "unknown";
$cmd = '/isis2/pgbin/valsub ' || '-u' || ISIS2_USER || ' ' || submission_num;

system($cmd);

   $query = "SELECT COUNT(*) FROM pend_file_results WHERE submission_no = " || submission_num || " AND code = '600' ";
   $rv = spi_exec_query($query);
   $count600 = $rv->{processed};

   if ($count600 = 1)
   {
      $result = 0; # JOB_ERRNO_OK
   }
   else
   {
      $result = 110; # JOB_101_ERRNO_VSUB_FAIL
   }

return($result);

$$ LANGUAGE plperlu IMMUTABLE ;

The above loads into the DB ok, but when I call it I get the following error msg:

error from Perl function "validatesubmission": syntax error at end of input at line 8.

I'm not sure what line 8 actually refers to because in the DB the code is:

CREATE OR REPLACE FUNCTION public.validatesubmission(submission_num text)
RETURNS integer
LANGUAGE plperlu
IMMUTABLE
AS $function$
$ISIS2_USER = "unknown";
$cmd = '/isis2/pgbin/valsub ' || '-u' || ISIS2_USER || ' ' || submission_num;

system($cmd);

   $query = "SELECT COUNT(*) FROM pend_file_results WHERE submission_no = " || submission_num || " AND code = '600'";
   $rv = spi_exec_query($query);
   $count600 = $rv->{processed};

   if ($count600 = 1)
   {
      $result = 0; # JOB_ERRNO_OK
   }
   else
   {
      $result = 110; # JOB_101_ERRNO_VSUB_FAIL
   }

return($result);
$function$

So that's also confused me. Any help would be appreciated.

Re: PLPERLU help

From

Michael Wood

Date:

30 June 2010, 11:58:52

On 30 June 2010 10:59, Atif Jung <atifjung@gmail.com> wrote:
> I have the following SPL written in plperlu which I need help with please.
>
> CREATE or replace FUNCTION validatesubmission(submission_num text) RETURNS
> INTEGER AS $$
>    $ISIS2_USER = "unknown";
>    $cmd = '/isis2/pgbin/valsub ' || '-u' || ISIS2_USER || ' ' ||
> submission_num;

I have never used plperlu, but I suspect your problem is the
concatenation operators you are attempting to use.  Perl uses "." for
concatenating strings, not "||".  So try changing those ||s to dots.

P.S.  What happens if this function is called with something other
than a number?  e.g. what if it includes shell meta-characters?

--
Michael Wood <esiotrot@gmail.com>