Re: Problems w/ Temp Tables

[Alan Hodgson <ahodgson(at)simkin(dot)ca>]

On Tuesday 23 January 2007 08:51, brian stapel <brians_224(at)hotmail(dot)com> 
wrote:
>
> Can you tell me where should I implement the EXECUTE commands - in my
> function or with in my vba code?  My vba code typically uses - SELECT
> * from {function name}({parameters} to execute the postgresql
> function.

You would use EXECUTE QUERY in place of all normal SQL statements that 
access the table within your function.  The trickiest part of this is 
proper quoting of user-supplied data.

However, one of my developers suggested a better way to handle this.  
Use an exception block around creating the temporary table:

BEGIN
    TRUNCATE my_temp_table;
EXCEPTION
    WHEN undefined_table THEN
         CREATE TEMP TABLE my_temp_table (cols);
END;

.. and don't drop the table at the end of the function.  This allows 
normal use of the table within the function, and will recreate it only 
once per session.  You may also need to take more care in naming the 
temporary table, since it will stick around in your session.

-- 
"Government big enough to supply everything you need is big enough to 
take everything you have ... the course of history shows that as a 
government grows, liberty decreases." -- Thomas Jefferson