Thread: question about temp table in function

I'm working on a plpgsql function that creates and populates a
temporary table.  I would like the function to first drop the temp
table, if it already exists.  I'm not sure how to accomplish this,
though.  My first inclination was to simply wrap the 'drop table'
command in an exception handling block and ignore the exception if the
table does not exist.  I'm not sure what error condition to catch,
though, so rather than specifying a "WHEN" condition, I just had
something like the following:

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?

Is there a better way to accomplish this?  I thought about querying
pg_tables, and seeing if a record exists.  Would that be a better
approach?

Thanks,
Tim

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/

On Nov 16, 2004, at 2:11 PM, Michael Fuhr wrote:
> \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/
>

That's exactly what I needed.  I didn't think to set the verbosity to
get the error code.  Thanks!