Re: error querying temp table in plpgsql function

Timothy Perrigo wrote:
> (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