> Date: Tue, 14 Jan 2003 11:37:26 +0100
> From: <comarin@telefonica.net>
>
> I need to write functions that uses temporary tables. The function has to
> create te temp table if it not exists, or delete the contents if it exists.
>
> But when I start a session and i lunch the function , the first time works,
> after it fails.
>
Using temporary tables in stored procedures is somewhat tricky.
Here are some tips:
- Use "create local temporary table" to make the table local to
the current transaction. Then other transactions may happily
use the same temporary table name at teh same time.
- All statements that refer to the temp table in your function
must be called with EXECUTE. This is because after the function
is compiled into the DB server, the tables are referenced via
OID rather than name. That's why your function works the first time,
but fails the second time when the original OID is no longer valid.
- Don't forget to drop your temp table somewhere in your function
(it will be dropped implicitly at the end of the transaction, but you
cannot be sure that the same function is not called more than once
in a single transaction).
Hope this helps,
Christoph Dalitz