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;
BEGINCREATE 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 usedso 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?
--
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