By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,541 Members | 1,476 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,541 IT Pros & Developers. It's quick & easy.

question about temp table in function

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #2

P: n/a

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!
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.