Обсуждение: Unable to handle error in plperl

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

On Wed, Jul 10, 2013 at 2:36 PM, Alex Lai <mlai@sesda3.com> wrote:
> 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);

Its this bit here that is tripping you up. Perhaps you meant
elog(INFO, ...) or something?

I know how elog works.  elog only show the status, but it does not allow me to execute another query when the current
queryfails because one of the invalid column was specified. 

Alex Lai

----- Original Message -----
From: "Alex Hunsaker" <badalex@gmail.com>
To: "Alex Lai" <mlai@sesda3.com>
Cc: pgsql-bugs@postgresql.org
Sent: Thursday, July 11, 2013 11:47:04 AM
Subject: Re: [BUGS] Unable to handle error in plperl

On Wed, Jul 10, 2013 at 2:36 PM, Alex Lai <mlai@sesda3.com> wrote:
> 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);

Its this bit here that is tripping you up. Perhaps you meant
elog(INFO, ...) or something?

On Mon, Jul 15, 2013 at 5:56 AM, Ming Lai <mlai@sesda3.com> wrote:
> I know how elog works.  elog only show the status, but it does not allow =
me to execute another query when the current query fails because one of the=
 invalid column was specified.

Hrm? Im not sure what you mean. If you elog(ERROR) outside of eval the
current transaction will be aborted. Thats why I suggested doing
elog(INFO) instead. The below example works fine for me. Perhaps you
can highlight exactly what you think it broken so I can understand?

=3D> begin;
BEGIN

=3D> create table a_table (a_column int);
CREATE TABLE

=3D> CREATE OR REPLACE FUNCTION foo() RETURNS text as $$
  my $sql =3D "";
  my $status =3D "";
  my $r =3D "";
  $sql =3D 'SELECT non_exist_column from a_table limit 1';
  eval { spi_exec_query($sql);};
  if ($@) {
     $status =3D 'invalid: '.$@;
     my $rv =3D spi_exec_query('SELECT true as col;');
     return "$status\nQuery after error: ".$rv->{rows}[0]{'col'};
 } else {
    $status =3D 'valid';
 }
 return $status;
$$ LANGUAGE plperl;
CREATE FUNCTION

=3D> select foo();
                             foo
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80
 invalid: column "non_exist_column" does not exist at line 6.=E2=86=B5
                                                             =E2=86=B5
 Query after error: t
(1 row)

=3D> select true;
 bool
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80
 t
(1 row)

=3D> commit;
COMMIT