Unable to handle error in plperl - Mailing list pgsql-bugs

Dear all,

I have a situation.  I am unable to pass control back to the function
once it hit the "undefined_column" error code.
I am not sure there's a way to return '123' instead exit from the function.

Here is my code

CREATE OR REPLACE FUNCTION foo() RETURNS text as $$
   my $sql = "";
   my $status = "";
   my $r = "";
   $sql = 'SELECT non_exist_column from a_table limit 1';
   eval { spi_exec_query($sql);};
   if ($@) {
      $status = 'invalid: '.$@;
      elog(ERROR, $status);
      return '123';
  } else {
     $status = 'valid';
  }
  return $status;
$$ LANGUAGE plperl;

When I run it

select foo();

ERROR: invalid: column "non_exist_column" does not exist at line 6.
CONTEXT:  PL/Perl function "foo"

When I select from the valid column
CREATE OR REPLACE FUNCTION foo() RETURNS text as $$
   my $sql = "";
   my $status = "";
   my $r = "";
   $sql = 'SELECT exist_column from a_table limit 1';
   eval { spi_exec_query($sql);};
   if ($@) {
      $status = 'invalid: '.$@;
      elog(ERROR, $status);
      return '123';
  } else {
     $status = 'valid';
  }
  return $status;
$$ LANGUAGE plperl;

When I run it

select foo();

    foo
----------
valid
(1 row)

--
Best regards,


Alex Lai
OMI SIPS DBA ADNET Systems , Inc.
7515 Mission Drive,
Suite A100 Lanham, MD 20706
301-352-4657 (phone)
301-352-0437 (fax)
mlai@sesda3.com