creating/droping a table from a function stored in public vs a sc hema

Hi,

I have a function that creates a table and at the end of the function
deletes the table. This function is called by a Crystal report. I created
this function under a schema(non public) and it works fine. I can refresh
the report at will.

I copied this function into another database, public schema, and the first
time I run the Crystal report which calls the function it works fine.
However if I attempt to refresh the data I get an error message that the
public.my_table relation cannot be found.

Is their a difference in design on how postgresql treats the creation of
tables in the public schema vs other schemas behind the scenes or would this
possibly be a bug?

CREATE OR REPLACE FUNCTION schema1.foo(date, date)
RETURNS SETOF record AS
'

DECLARE


from_submit_date ALIAS FOR $1;
to_submit_date ALIAS FOR $2;


BEGIN

cnt := (select count(relname) from pg_class where relname = \'my_table\');

IF cnt = 0 then

CREATE TABLE schema1.my_table
(
field text,
);
ELSE
delete from schema1.my_table;
END IF;

for r in EXECUTE \'select * from schema1.my_table\' loop
return next r;
end loop;


cnt := (select count(relname) from pg_class where relname = \'my_table\');

IF cnt <> 0 then
drop table schema1.my_table;
END IF;

return;
END;

'
LANGUAGE 'plpgsql' STABLE;

Michael Godshall
GMAC Global Relocation <http://www.gmacglobalrelocation.com/> Services
900 So. Frontage Road
Woodridge, IL 60517
630-427-2070 office
630-972-2287 fax

michael_godshall(at)gmachs(dot)com <mailto:michael_godshall(at)gmachs(dot)com>