Re: Temp Tables

mdb002(at)yahoo(dot)com (mdb) wrote in message news:<9e6ea67a(dot)0203041248(dot)24859ff2(at)posting(dot)google(dot)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