Re: question about temp table in function - Mailing list pgsql-general

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/