On Tue, Nov 16, 2004 at 01:39:45PM -0600, Timothy Perrigo wrote:
> begin
> execute 'drop table my_temp';
> exception
> -- do nothing
> end;
>
> That didn't work; apparently the "WHEN" condition is necessary. What
> condition should I be trapping for?
I assume you're using one of the 8.0 betas; earlier versions of
PostgreSQL didn't have exception handling.
Appendix A of the documentation shows the conditions you can trap.
Here's a way to find out what exception you need without having to
search the entire list:
CREATE OR REPLACE FUNCTION foo() RETURNS BOOLEAN AS $$
BEGIN
DROP TABLE my_temp;
RETURN TRUE;
END;
$$ LANGUAGE plpgsql;
\set VERBOSITY verbose
SELECT foo();
ERROR: 42P01: table "my_temp" does not exist
CONTEXT: SQL statement "DROP TABLE my_temp"
PL/pgSQL function "foo" line 2 at SQL statement
LOCATION: DropErrorMsgNonExistent, utility.c:144
The error code is 42P01, which Appendix A shows as UNDEFINED TABLE.
The exception-handling block would therefore be:
BEGIN
DROP TABLE my_temp;
EXCEPTION
WHEN undefined_table THEN
NULL;
END;
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/