Re: Temp Tables

Scott,
Thanks for the response.

However, I have further narrowed down the problem.  The temp tables
were not the problem.  I was creating views of the temp tables which
was the problem.  Now why would I create a view of temp tables you
ask?  I am using VB, before I get blasted for this heresy it was
originally an access and vb application that we are upgrading to
PostgreSQL.  The code is substantial and to recode everthing would be
a substantial undertaking.  Back to the problem the VB connection does
not see the temp table if I do not use a view.  A little bit about the
temp table it is used to emulate an access crosstab query and columns
are added and removed (table deleted and columns added) multiple times
as the users enter data.  I display the data in DBGrid using a
recordset and setting the dbgrid datasource property (no
dataenvironment or datacontrol used).  Has anybody used temp tables to
display data with vb and how do you do it?

Marc
smarlowe@ihs.com (Scott Marlowe) wrote in message news:<a03059a3.0203071416.7bf79d27@posting.google.com>...
> mdb002@yahoo.com (mdb) wrote in message news:<9e6ea67a.0203041248.24859ff2@posting.google.com>...
> > Hello,
> >
> > I am running a client server app and am using temp tables to do some
> > data manipulation.  However, Postgresql seems to prevent multiple temp
> > tables from being created with the same name.  The temp tables are
> > created by seperate connections to the database, but this does not
> > seem to matter.  Is this correct?  Do I need to make the names unique
> > by using a user id?  Am I doing something wrong?  I am using
> > Postgresql 7.1.3 and odbc 7.1.9.
>
> Common mistake.  Basically, you should be doing this inside of a
> transaction block so that that the temp table is hidden from all the
> other users, like so:
>
> begin;
> create temp table test (name text, id int);
> (do mysterious and wonderful things in SQL here)
> end;
>
> the temp table will be built, seen only by your transaction, then
> automatically dropped on exit.
>
> note that SEQUENCES should not be created, i.e. don't do 'create temp
> table yada (name text, id serial)' because that requires the building
> of a sequence, which isn't a temporary kind of thing.  you can
> reference a sequence within a transacation block in a temp table
> though, like so
>
> create temp table test (name text, id int not null default
> nextval('"test_id_seq"'::text));
>
> Good luck