(PostgreSQL 8.0 beta 4 on Mac OS X 10.3.6)
I'm working on a function which creates and populates a temporary table, then returns the number of records it has inserted. I'm getting an error, though, after successive invocations of the function (I can call it once successfully, but on the next call I get an error). I've been able to reproduce the error with the following sample function:
select count(*) into result from test;
If, instead of executing the "select count(*)" directly, I use the EXECUTE command, then everything works. Is this expected behavior?
Yes - the reference to table "test" will be compiled down to it's OID. When you re-create the test table it will have a new OID and so you get an error.
As you say, you need to use EXECUTE in cases like this with plpgsql. An interpreted language (pltcl/plperl) should be OK in situations like this, though at the cost of parsing the query each time the function is called.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
