Postgres 8.0 -> BEGIN EXCEPTION END Syntax????

Daniel Schuchardt wrote:

Hi ,

can anybody tell me the postgres - syntax for begin excepion end if I
want to ignore the error?

BEGIN
exception ...
EXCEPTION
WHEN OTHERS THEN ?????????????what to write for do nothing?????????
END;

in oracle it's

WHEN OTHERS THEN null; but this syntax doesn't work in postgres.

This for sure is not the best solution, but try:

WHEN OTHERS THEN PERFORM 0;

Regards
Gaetano Mendola

Daniel Schuchardt <daniel_schuchardt@web.de> writes:

BEGIN
exception ...
EXCEPTION
WHEN OTHERS THEN ?????????????what to write for do nothing?????????
END;

in oracle it's
WHEN OTHERS THEN null;
but this syntax doesn't work in postgres.

In Postgres you just write nothing at all:

BEGIN
...
EXCEPTION
WHEN OTHERS THEN
END;

However, it does appear that Oracle's PL/SQL has such a statement,
and that they don't like empty exception sections (or empty if/then/else
arms, etc), but *require* you to write "NULL;" in these places.
It seems to me that it would ease porting of Oracle functions if
we allowed a NULL statement in plpgsql.

It looks like about five minutes' work to add such a thing ... anyone
have any objections?

regards, tom lane

Tom Lane said:

Daniel Schuchardt <daniel_schuchardt@web.de> writes:

BEGIN
exception ...
EXCEPTION
WHEN OTHERS THEN ?????????????what to write for do nothing?????????
END;

in oracle it's
WHEN OTHERS THEN null;
but this syntax doesn't work in postgres.

In Postgres you just write nothing at all:

BEGIN
...
EXCEPTION
WHEN OTHERS THEN
END;

However, it does appear that Oracle's PL/SQL has such a statement, and
that they don't like empty exception sections (or empty if/then/else
arms, etc), but *require* you to write "NULL;" in these places.
It seems to me that it would ease porting of Oracle functions if
we allowed a NULL statement in plpgsql.

It looks like about five minutes' work to add such a thing ... anyone
have any objections?

It's got my vote :-) PLSQL is based on Ada and this is an Ada rule. I like
the PLSQL/Ada way a lot more than the PostgreSQL example you gave above,
which just looks ... odd.

cheers

andrew

On Mon, 16 Aug 2004, Tom Lane wrote:

in oracle it's
WHEN OTHERS THEN null;
but this syntax doesn't work in postgres.

It looks like about five minutes' work to add such a thing ... anyone
have any objections?

Is NULL above an empty statement in oracle or is it a normal expression
(the normal NULL value) so that the above is a shorthand for

WHEN OTHERS THEN SELECT NULL;

?

--
/Dennis Bj�rklund

Dennis Bjorklund <db@zigo.dhs.org> writes:

Is NULL above an empty statement in oracle or is it a normal expression

It's an actual empty statement.

(the normal NULL value) so that the above is a shorthand for
WHEN OTHERS THEN SELECT NULL;

Even if it were, the point here is to accept standard Oracle coding
without any translation of that sort.

regards, tom lane