Hi all,
I have a function as follows:
<code>
[gaurav@linuxserver gaurav]$ cat foo
DROP FUNCTION foo();
CREATE FUNCTION foo() RETURNS INTEGER AS '
BEGIN
DROP TABLE foo_1;
CREATE TABLE foo_1 AS SELECT x FROM foo;
DROP TABLE foo_2;
CREATE TABLE foo_2 AS SELECT x FROM foo_1;
RETURN 1;
END;
' LANGUAGE 'plpgsql';
[gaurav@linuxserver gaurav]$
</code>
Now my problem is as follows:
<problem>
test=> SELECT foo();
foo
-----
1
(1 row)
test=> SELECT foo();
ERROR: Relation 5483738 does not exist
test=>
test=> SELECT relname, relfilenode FROM pg_class WHERE relfilenode=5483738;
relname | relfilenode
---------+-------------
foo_1 | 5483738
(1 row)
test=>
test=> \i foo
DROP
CREATE
test=> SELECT foo();
foo
-----
1
(1 row)
test=> SELECT foo();
ERROR: Relation 5483812 does not exist
test=>
test=> \q
[gaurav@linuxserver gaurav]$ psql test
<snip/>
test=> SELECT foo();
foo
-----
1
(1 row)
test=>
test=> SELECT foo();
ERROR: Relation 5483848 does not exist
test=>
test=> \q
</problem>
So as you can see, I get a "relation does not exist" error on a
function that is very much there.
Three observations:
1. When I drop the function and create it again, it
works once before the error is back.
2. I already tried was putting BEGIN-END blocks around the two
DROP/CREATE TABLE pairs.
3. The Delphi app that calls this function (which actually prepares a
table that feeds a report) has to be restarted between invocations of
the report.
One solution would be to create these tables right at the outset
and only truncate them each time a report is to be created with
different parameters. However this does not deliver for the reports
where the table is itself generated dynamically depending on
parameters passed to the function.
Thanks in advance
Regards,
Gaurav.
--
Sleep: A completely inadequate substitute for caffeine.
