Re: [GENERAL] 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 <[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/
Re: [GENERAL] Temp Table Within PLPGSQL Function - Something Awry
On Tuesday 16 January 2007 10:10, "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 query plans for all the references to the table get cached the first time the function is run in a session. These cached plans include the table's oid. This oid is not the same after you drop and recreate the table, unfortunately, and the cached plans are not invalidated. You can fix this a few ways. Use EXECUTE QUERY for all queries that reference the table. Or ... Don't drop the table at the end of the function. Use something like this at the beginning instead: BEGIN TRUNCATE temp_table; EXCEPTION WHEN undefined_table THEN CREATE TEMP TABLE temp_table (field type, ...); END; OTHER code; This will work better for you, although the table will continue to exist between calls in the same session. -- "A government that robs Peter to pay Paul can always depend upon the support of Paul." - George Bernard Shaw ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Temp Table Within PLPGSQL Function - Something Awry
On Tue, Jan 16, 2007 at 11:10:25AM -0700, Lenorovitz, Joel 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. Known problem, I beleive it's even mentioned in the docs. Basically, if you use temp tables in pl/pgsql, you have to use EXECUTE for the statements referring to it. The issue is that pl/pgsql is caching the plan and so tries to use the plan with the old temp table after the table has gone. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
[GENERAL] Temp Table Within PLPGSQL Function - Something Awry
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=#
