error querying temp table in plpgsql function - Mailing list pgsql-general

(PostgreSQL 8.0 beta 4 on Mac OS X 10.3.6)

I'm working on a function which creates and populates a temporary
table, then returns the number of records it has inserted.  I'm getting
an error, though, after successive invocations of the function (I can
call it once successfully, but on the next call I get an error).  I've
been able to reproduce the error with the following sample function:

create or replace function test() returns integer as
$$
declare result integer;
begin

-- drop temp table, if it exists (ignore exception if it doesn't)
begin
         execute 'drop table test';
exception
         when undefined_table then
         null; -- do nothing
end;

-- create the vehicle route table
execute 'create temp table test ('
         || 'seq_num serial not null, '
         || 'foo text'
         || ')';

select count(*) into result from test;

return result;

end;
$$
language 'plpgsql';

Here is a clipping of a psql session which creates the function and
calls it twice, along with the error that results:

silo=# \i test.sql
CREATE FUNCTION
silo=# select test();
NOTICE:  CREATE TABLE will create implicit sequence "test_seq_num_seq"
for serial column "test.seq_num"
CONTEXT:  SQL statement "create temp table test (seq_num serial not
null, foo text)"
PL/pgSQL function "test" line 13 at execute statement
  test
------
     0
(1 row)

silo=# select test();
NOTICE:  CREATE TABLE will create implicit sequence "test_seq_num_seq"
for serial column "test.seq_num"
CONTEXT:  SQL statement "create temp table test (seq_num serial not
null, foo text)"
PL/pgSQL function "test" line 13 at execute statement
ERROR:  relation with OID 524907 does not exist
CONTEXT:  SQL statement "SELECT  count(*) from test"
PL/pgSQL function "test" line 18 at select into variables
silo=#

If, instead of executing the "select count(*)" directly, I use the
EXECUTE command, then everything works.  Is this expected behavior?

Thanks,
Tim