Skip site navigation (1) Skip section navigation (2)

Re: Problems w/ Temp Tables

From: Alan Hodgson <ahodgson(at)simkin(dot)ca>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Problems w/ Temp Tables
Date: 2007-01-23 17:19:14
Message-ID: 200701230919.14235@hal.simkin.ca (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

pgsql-novice by date

Next:From: A. KretschmerDate: 2007-01-23 17:25:21
Subject: Re: Problems w/ Temp Tables
Previous:From: brian stapelDate: 2007-01-23 16:51:51
Subject: Problems w/ Temp Tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group