Re : Re : Select in temporary table

> 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/

---------------------------

Hello,

Thank you for answering me.
You're right, my temporary table is done like that : CREATE TEMP TABLE

My application doesn't disconnect, between two executions because sometimes, it works two, three, four, .. times and I've got the error on the next execution.

I have multiple connections that's right, but these connections are in the same session.
If I insert data in my table, juste after creating it (when launching my application), the good number of rows is displayed.
Then sometimes, the same number is displayed on the second execution. etc...

I haven't developed the core, so I don't know exactly how it works.
But I'm sure there is no disconnection between these two executions.

Thank you very much
----------------------------

Hello,

Another test has been done, the first thing I'm doing in my PL/PGSQL function is :

IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'myTableTemp') THEN
raise notice 'function exists...';
ELSE
raise notice 'function not exists...';
END IF;

So my function is like that :

CREATE OR REPLACE FUNCTION test() RETURNS integer AS
$BODY$
DECLARE

nbLines integer;

BEGIN

IF EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'myTableTemp') THEN
raise notice 'function exists...';
ELSE
raise notice 'function not exists...';
END IF;

-- Select from myTableTemp
SELECT COUNT(distinct pat_id) INTO nbLines FROM instance_fiche WHERE inst_id IN (select inst_id from myTableTemp);


RETURN nbLines;

EXCEPTION
WHEN OTHERS THEN
BEGIN
raise exception 'Erreur function test() : %', SQLERRM;
END;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

My function notices everytime 'function exists', even when I've got the error "relation myTableTemp does not exist".

Any ideas ? I'm a little bit disappointed with this problem...

Thanks a lot