Re: plpgsql: function throws error on second call! - Mailing list pgsql-general

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.