[BUGS] BUG #3718: Unexpected undefined_table error after creating/dropping tables

[Dean]

The following bug has been logged online:

Bug reference:      3718
Logged by:          Dean
Email address:      [EMAIL PROTECTED]
PostgreSQL version: 8.2.5
Operating system:   Linux (opensuse 10.3 64-bit) and Windows 2000 SP4
Description:        Unexpected undefined_table error after creating/dropping
tables
Details: 

If I create a function which relies on the undefined_table exception to test
if a table exists, it does not behave as expected. Here's the simplest
example I could come up with:

CREATE OR REPLACE FUNCTION foo() RETURNS text AS
$$
BEGIN
  DELETE FROM bar;
  RETURN 'Table exists';
EXCEPTION
  WHEN undefined_table THEN
    RETURN 'Table missing';
END;
$$ LANGUAGE plpgsql VOLATILE;

DROP TABLE IF EXISTS bar;
SELECT foo();
CREATE TABLE bar(dummy int);
SELECT foo();
DROP TABLE bar;
SELECT foo();
CREATE TABLE bar(dummy int);
SELECT foo();

The 4 calls to foo() return

Table missing
Table exists
Table missing
Table missing

I expect the final call to foo() to return 'Table exists' not 'Table
missing'.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend