Temporary table in pl/pgsql

Hello again all,

I'm using a temporary table in a pl/PgSQL function, and I've read the
bit in the FAQ about using EXECUTE to force the table-creation query to
be re-evaluated each time the function is called.

However, it doesn't seem to work for me. The first time I call the
function, all is well; the second (and every subsequent) time, the
function bombs with the 'relation with OID XXXXX does not exist' error -
this seems to imply that the EXECUTE statement is getting cached along
with the rest of the function, which according to the docs ain't
supposed to happen :-(

I'm using 8.2.3 on WinXP.

Here's the first part of the function - I'll supply a complete test case
if necessary:

-------------------------------------------------
create or replace function tutor_availability_remaining
(
   aTerm integer,
   anInstrument varchar,
   aLevel varchar
)
returns setof tutor_availability
as
$$
declare
   OrigBlock tutor_availability%rowtype;
   SlotsForDay teachingslots%rowtype;
begin
   -- Create a termporary table to hold the results.
   -- Use EXECUTE to force this to be executed each
   -- time, as per the FAQ.
   execute 'create temporary table TheResults('
     || 'block_id integer, term_id integer, term_name varchar(40), '
     || 'the_date date, month_name varchar(12), day_name varchar(12), '
     || 'is_weekend boolean, tutor_id integer, surname varchar(40), '
     || 'firstname varchar(40), block_starts time without time zone, '
     || 'block_ends time without time zone)';

   -- stuff snipped here...
   -- etc etc

----------------------------------------

I wondered if, the string passed to EXECUTE, being entirely literal, it
was somehow getting over-optimised :) and I tried changing the second
line above to use one of the parameters passed in, thus -

   'term_id integer default ' || quote_literal(aTerm) ...

- but it made no difference.

What am I missing?

TIA,

Ray.


---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------