Re: Unable to handle error in plperl

On Mon, Jul 15, 2013 at 5:56 AM, Ming Lai <mlai(at)sesda3(dot)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?

=> begin;
BEGIN

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

=> 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: '.$@;
my $rv = spi_exec_query('SELECT true as col;');
return "$status\nQuery after error: ".$rv->{rows}[0]{'col'};
} else {
$status = 'valid';
}
return $status;
$$ LANGUAGE plperl;
CREATE FUNCTION

=> select foo();
foo
──────────────────────────────────────────────────────────────
invalid: column "non_exist_column" does not exist at line 6.↵
↵
Query after error: t
(1 row)

=> select true;
bool
──────
t
(1 row)

=> commit;
COMMIT