Temp Table Within PLPGSQL Function - Something Awry

From: "Lenorovitz, Joel" <Joel(dot)Lenorovitz(at)usap(dot)gov>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Temp Table Within PLPGSQL Function - Something Awry
Date: 2007-01-16 18:10:25
Message-ID: 7119BB016BDF6445B20A4B9F14F50B2D44A906@WILSON.usap.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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=#

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruno Wolff III 2007-01-16 18:11:58 Re: Performance with very large tables
Previous Message Tom Lane 2007-01-16 18:07:21 Re: Elegant copy of a row using PL