Consider the following scenario. I have a bunch of user defined functions, f_i, that create, populate, and use a local temporary table with the same name res:
create function f_i()
returns table( … )
begin
# Result stats table.
create local temporary table res(
param1 int,
param2 int
);
/* Populate res */
/* Use res in some queries */
return table(
/* Query involving res */
);
end;
My first question is: Does create local temporary table x create a table that is disposed/dropped as soon as the function where it was declared finishes execution? I assumed this was the case, but I’m now skeptical as we are running into all sorts of concurrency issues that point to the res table not being cleared out properly from memory.
This takes me to the second question: How do I drop a temporary table if such table exists? The reason for this is to prevent table create exceptions (which we are currently running into). I’d like to achieve something along these lines:
create function f_i()
returns table( … )
begin
# Drop local temporary table if such table exists.
drop local temporary table res; <-------- How do I do this in MonetDB?
# Result stats table.
create local temporary table res(
param1 int,
param2 int
);
/* Populate res */
/* Use res in some queries */
return table(
/* Query involving res */
);
end;
Where, as you can see, I want to explicitly drop the local temporary table res if such exists. If I leave the local temporary markers in the drop statement, MonetDB complains that it found unexpected LOCAL or TEMPORARY tokens. I wouldn’t like to remove these markers because a statement like:
drop table res;
will drop any table in the default function schema with that name.
Any help and hints are well appreciated!
