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 Services
900 So. Frontage Road
Woodridge, IL 60517
630-427-2070 office
630-972-2287 fax
michael_godshall@gmachs.com