Temporary tables

["Raymond O'Donnell" <rod@xxxxxx>]

Hi all,

When you create a temporary table using the CREATE TEMPORARY TABLE 
AS... syntax, does the table get created (but left empty) if the 
query returns no rows?

I'm seeing funny behaviour, and don't know whether it's my incomplete 
understanding or whether something weird really is happening. Here's 
a test case.....

-----[begin]-----

create table t1(f1 int4);

create or replace function testfunc() returns int4 as
$$
declare
  TempInt int4;
begin
  create temporary table TestTbl as
    select * from t1;
  select count(*) into TempInt from TestTbl;
  drop table TestTbl;

  return TempInt;
end;
$$ language 'plpgsql';

-----[end]-----

The first time I call "select testfunc();", I get 0 as expected. 
However, subsequent calls return an error:

ERROR:  relation with OID 80845 does not exist
CONTEXT:  SQL statement "SELECT  count(*) from TestTbl"
PL/pgSQL function "testfunc" line 7 at select into variables

Why should the temporary table be there the first time around and not 
after that, since it's created anew at the start of the function?

Thanks for any help!

--Ray O'Donnell

-------------------------------------------------------------
Raymond O'Donnell     http://www.galwaycathedral.org/recitals
rod@xxxxxx                          Galway Cathedral Recitals
-------------------------------------------------------------