It appears that what is happening is PL/pgSQL is caching the table
definition (it appears to do this on first execution), testing it with
dynamic SQL via the EXECUTE clause doesn't exhibit the same issue:
CREATE OR REPLACE FUNCTION test_fxn() RETURNS SETOF RECORD AS $$
DECLARE
test_rec RECORD;
BEGIN
EXECUTE 'CREATE TEMP TABLE temp_tbl (actual_inventory_id BIGINT, cal_due
TIMESTAMP)';
FOR test_rec IN SELECT id FROM item LOOP
EXECUTE 'INSERT INTO temp_tbl (actual_inventory_id) values (6)';
END LOOP;
FOR test_rec IN EXECUTE 'SELECT actual_inventory_id FROM temp_tbl' LOOP
RETURN NEXT test_rec;
END LOOP;
EXECUTE 'DROP TABLE temp_tbl';
RETURN;
END;
$$ LANGUAGE PLPGSQL;
On 1/16/07, Lenorovitz, Joel <[EMAIL PROTECTED]> wrote:
Greetings,
I am trying to work with a TEMP TABLE within a plpgsql function and I was
wondering if anyone can explain why the function below, which is fine
syntactically, will work as expected the first time it is called, but will
err out as shown on subsequent calls. The DROP TABLE line seems to be
executing (note \d results on temp_tbl), and repeatedly
adding/dropping/querying temp_tbl from the command line also works without a
problem. However, when it's all put into the function and cycled through
multiple times then something seems to be getting confused. Any light that
can be shed on this peculiarity would be great. Once I get past this hurdle
the function will, of course, go on to do more and make better use of the
temp table, but for now I just need to figure out why it's failing. Is this
an improper or ill-advised use of a temp table?
Thanks much,
Joel
CREATE OR REPLACE FUNCTION test_fxn() RETURNS SETOF RECORD AS $$
DECLARE
test_rec RECORD;
BEGIN
CREATE TEMP TABLE temp_tbl (actual_inventory_id BIGINT, cal_due
TIMESTAMP);
FOR test_rec IN SELECT id FROM item LOOP
INSERT INTO temp_tbl (actual_inventory_id) values (6);
END LOOP;
FOR test_rec IN SELECT actual_inventory_id FROM temp_tbl LOOP
RETURN NEXT test_rec;
END LOOP;
DROP TABLE temp_tbl;
RETURN;
END;
$$ LANGUAGE PLPGSQL;
postgres=# select max(id) from test_fxn() AS (id bigint);
max
-----
6
(1 row)
postgres=# select max(id) from test_fxn() AS (id bigint);
ERROR: relation with OID 24449 does not exist
CONTEXT: SQL statement "INSERT INTO temp_tbl (actual_inventory_id) values
(6)"
PL/pgSQL function "test_fxn" line 6 at SQL statement
postgres=# \d temp_tbl;
Did not find any relation named "temp_tbl".
postgres=#
--
Chad
http://www.postgresqlforums.com/
