Re: Select in temporary table

In response to Sylvain Lara <sly_lara(at)yahoo(dot)com>:

> Hello,
>
> I found some subjects like mine on many forums, but the given solution doesn't work for me.
>
> I'm using PostgreSQL 8.4. I'm working on a C# application.
>
> When connecting to the application, a temporary table myTableTemp is created.
> This table is available for all the application duration, and is deleted when the session is killed, when user closes the application.
>
> The use should do this, after connnecting to my application :
>
> - Menu item 1 : A first PL/PGSQL function inserts data in this table (and temporary table already exist because created when launching application)
> - Menu item 2 : Another PL/PGSQL function selects number of rows in this table and displays it
>
> As I encountered my problem, I have just tried the second function (called by menu item 2), that should return me 0, because the temporary table has not be filled.
>
> When launching a first time the menu item 2, the function returns 0 ==> good result
> When launching a second time the menu item 2, I've got the following error ==> table myTableTemp does not exist.
>
> Sometimes, the menu item 2 works many times, if I'm waiting a little time between two executions, but at the end, the same error occurs.
>
>
> I found on some posts solutions talking about using the EXECUTE function, which I tried, but the same result still occurs.
>
>
> My code is (not EXACTLY my code because just written by memory) :
>
> CREATE OR REPLACE FUNCTION test RETURN integer AS
> DECLARE
>         nbLines    integer;
>        
> BEGIN
>        
>         EXECUTE 'select count(*) from myTableTemp' INTO nbLines;
>        
>         RETURN nbLines;
>        
> END;
>
>
> First execution :
> select test() ==> 0
>
> Second execution :
> select test() ==> table myTableTemp does not exist.
>
> Any ideas ?

I assume you're using CREATE TEMP TABLE to make the table. Internally,
PostgreSQL automatically drops the table when the session is disconnected.
Also, the temp table is not visible from any other connection.

As a result, my guess is that either:
a) Your application disconnects and reconnects between the two runs
b) Your application establishes multiple connections at some point and
uses a different one on the second run

It may be deep in the underlying libraries that this is happening. I have
seen examples of code that establishe dozens of database connections for
a single application, because the code is poorly organized (as an example).

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/