Re: Unable to handle error in plperl

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