[GENERAL] error querying temp table in plpgsql function

[Timothy Perrigo]

(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:

create or replace function test() returns integer as
$$
declare result integer;
begin
-- drop temp table, if it exists (ignore exception if it doesn't)
begin
execute 'drop table test';
exception
when undefined_table then
null; -- do nothing
end;
-- create the vehicle route table
execute 'create temp table test ('
|| 'seq_num serial not null, '
|| 'foo text'
|| ')';
select count(*) into result from test;
return result;
end;
$$
language 'plpgsql';
Here is a clipping of a psql session which creates the function and 
calls it twice, along with the error that results:

silo=# \i test.sql
CREATE FUNCTION
silo=# select test();
NOTICE:  CREATE TABLE will create implicit sequence test_seq_num_seq 
for serial column test.seq_num
CONTEXT:  SQL statement create temp table test (seq_num serial not 
null, foo text)
PL/pgSQL function test line 13 at execute statement
 test
--
0
(1 row)

silo=# select test();
NOTICE:  CREATE TABLE will create implicit sequence test_seq_num_seq 
for serial column test.seq_num
CONTEXT:  SQL statement create temp table test (seq_num serial not 
null, foo text)
PL/pgSQL function test line 13 at execute statement
ERROR:  relation with OID 524907 does not exist
CONTEXT:  SQL statement SELECT  count(*) from test
PL/pgSQL function test line 18 at select into variables
silo=#

If, instead of executing the select count(*) directly, I use the 
EXECUTE command, then everything works.  Is this expected behavior?

Thanks,
Tim
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [GENERAL] error querying temp table in plpgsql function

[Richard Huxton]

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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]