Обсуждение: plpgsql: function throws error on second call!
hi, i cant get it right on my own. i ve tried to find something in the bug reports and there was a bug with temp tables in functions years ago. but it was fixed.. so here s my problem: CREATE FUNCTION testpunkte (int4) RETURNS int4 AS ' DECLARE var_id ALIAS FOR $1; var_count int4 := 0; BEGIN CREATE TEMP TABLE temp_punkte AS SELECT * FROM tmp where id = var_id; UPDATE real SET val1 = temp_punkte.val1 WHERE id = temp_punkte.id; GET DIAGNOSTICS var_count = ROW_COUNT; DROP TABLE temp_punkte; RETURN var_count; END; ' language 'plpgsql'; it is just a dummy function on some testdata in my testdatabase but if i call it two times: testarea=# select testpunkte(1); testpunkte ------------ 1 (1 row) fisrt time is fine and second time: testarea=# select testpunkte(1); NOTICE: Error occurred while executing PL/pgSQL function testpunkte NOTICE: line 9 at SQL statement ERROR: Relation 7842984 does not exist it seems to me the temp table is not generated in the second call of the function. but i dont know anything about system tables and so on so i cant check it. any hints or am i just stupid and dont see a typo?? but if the function makes it right on the first call it cant be a typo. hmm. looks strange to me. can anybody give me small hint, please? kind regards janning
On Fri, 26 Jul 2002, Janning Vygen wrote: > i cant get it right on my own. i ve tried to find something in the > bug reports and there was a bug with temp tables in functions years > ago. but it was fixed.. > > so here s my problem: > > CREATE FUNCTION testpunkte (int4) RETURNS int4 AS ' > DECLARE > var_id ALIAS FOR $1; > var_count int4 := 0; > BEGIN > > CREATE TEMP TABLE temp_punkte AS > SELECT * FROM tmp where id = var_id; > > UPDATE real > SET val1 = temp_punkte.val1 > WHERE id = temp_punkte.id; > GET DIAGNOSTICS var_count = ROW_COUNT; > DROP TABLE temp_punkte; > > RETURN var_count; > END; > ' language 'plpgsql'; If you're going to create/drop a table in a function, you'll want to use execute any time you're working with the table, otherwise it'll cache the plan from the original table that you've dropped.
Am Freitag, 26. Juli 2002 19:42 schrieb Stephan Szabo: > If you're going to create/drop a table in a function, > you'll want to use execute any time you're working with the > table, otherwise it'll cache the plan from the original table > that you've dropped. Thanks a lot!! i ve posted it to the interactive docs (of course mentioning your name), because i love the interactive docs with php but the postgresql docs are less used so there is one thing i can do for postgresql: putting all my newbie experience into the idoc :-) kind regards, janning
EXECUTE is mentioned in the FAQ on the web site. --------------------------------------------------------------------------- Janning Vygen wrote: > Am Freitag, 26. Juli 2002 19:42 schrieb Stephan Szabo: > > If you're going to create/drop a table in a function, > > you'll want to use execute any time you're working with the > > table, otherwise it'll cache the plan from the original table > > that you've dropped. > > Thanks a lot!! i ve posted it to the interactive docs (of > course mentioning your name), because i love the interactive docs > with php but the postgresql docs are less used > > so there is one thing i can do for postgresql: putting all my newbie > experience into the idoc :-) > > kind regards, > janning > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026