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@xxxxxx
---------------------------------------------------------------
