Re: Problems w/ Temp Tables

On Tuesday 23 January 2007 08:51, brian stapel <brians_224@hotmail.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