Re: temp tables and pg_pconnect()

[Emmanuel SARACCO <esaracco@xxxxxxx>]

hi adam,

thanks for your answer but here is part of my stored procedure:

------------------
	CREATE TEMP TABLE res_tot (tot INTEGER);

	sqlreq :=  '		
		INSERT INTO
			res_tot
		SELECT 					
			count(no_dossier) 	
		FROM ' ||
			vs_lb
		||' 		
		WHERE 1=1 ';

	IF surf_min>0 THEN
		sqlreq :=sqlreq||' AND surf_hab >= '|| surf_min ||' ';
	end if;

	IF surf_max>0 THEN
		sqlreq :=sqlreq||' AND surf_hab <= '|| surf_max ||' ';
	end if;
	
	IF prix_min>0 THEN
		IF devise = 'FRANC' THEN
			sqlreq :=sqlreq||' AND prix_franc >= '|| prix_min ||'  ';
		ELSE
			sqlreq :=sqlreq||' AND prix_euro >= '|| prix_min ||'  ';
		END IF;
	END IF;
	
	EXECUTE sqlreq;

	SELECT INTO resultat tot FROM res_tot;
	RETURN resultat;
------------------

as you see, I can not create a normal table because of concurrency 
problems. the solution would be to create a normal table with random 
generated name but I could not retrieve a value from it with a "execute" 
instruction... so I think it is impossible for me to use pg_pconnect() :-(

bye




Adam Lang wrote:

> Are temp tables supposed to be destroyed when the connection is cosed?  If
> so, then the reason is because with pconnect, the connection really is never
> killed as far as Postgresql sees it.  The connection remains open.
>
> Can you drop the temp table yourself through the pg_exec comand and just use
> DROP TABLE ?
>
> Adam Lang
> Systems Engineer
> Rutgers Casualty Insurance Company
> http://www.rutgersinsurance.com
> ----- Original Message -----
> From: "Emmanuel SARACCO" <esaracco@xxxxxxx>
> To: <pgsql-php@xxxxxxxxxxxxxx>
> Sent: Saturday, October 13, 2001 1:14 PM
> Subject: [PHP] temp tables and pg_pconnect()
>
>
>
> > hi,
> >
> > I am using temp tables in stored procedures (I just can not create
> > normal tables because I must retreive values from those tables in those
> > stored procedures with the "execute" instruction).
> >
> > doing connexions with pg_connect()/pg_close() on each php page all work
> > fine, but when I try to use pg_pconnect() (wich is much more efficient!)
> > temp table are not detroyed...
> >
> > how can I use pg_connect() and continue using temp tables in stored
> > procedures?
> >
> > thanks
> >
> >
> > --
> > Emmanuel SARACCO
> > Email: esaracco@xxxxxxx
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org



--
Emmanuel SARACCO
Email: esaracco@xxxxxxx