Re: Temp Table Within PLPGSQL Function - Something Awry

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 <Joel(dot)Lenorovitz(at)usap(dot)gov> 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/