Обсуждение: 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