Thread: About temporary table
HI, I installed postgreSql v7.2. There is a question about temporary tables. How can I drop a temporary table only if it exists? Thank you! JACK
jack wrote: > HI, > > I installed postgreSql v7.2. There is a question about temporary tables. How > can I drop a temporary table only if it exists? Thank you! Uh, just issue the drop and ignore the possible error. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
But when I write SQL in pl/pgSQL, I can't test to drop a temporary table. If it fails, the whole procedure will be aborted. That's my problem! JACK ----- Original Message ----- From: "Bruce Momjian" <pgman@candle.pha.pa.us> To: "jack" <datactrl@tpg.com.au> Cc: <pgsql-sql@postgresql.org> Sent: Monday, March 25, 2002 1:54 PM Subject: Re: [SQL] About temporary table > jack wrote: > > HI, > > > > I installed postgreSql v7.2. There is a question about temporary tables. How > > can I drop a temporary table only if it exists? Thank you! > > Uh, just issue the drop and ignore the possible error. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
Why don't you just create it at the start of where it is needed and then never worry about it. It is automatically deleted when the session finishes. On 25 Mar 2002 at 14:11, jack wrote: > But when I write SQL in pl/pgSQL, I can't test to drop a temporary table. If > it fails, the whole procedure will be aborted. That's my problem! > > JACK > > ----- Original Message ----- > From: "Bruce Momjian" <pgman@candle.pha.pa.us> > To: "jack" <datactrl@tpg.com.au> > Cc: <pgsql-sql@postgresql.org> > Sent: Monday, March 25, 2002 1:54 PM > Subject: Re: [SQL] About temporary table > > > > jack wrote: > > > HI, > > > > > > I installed postgreSql v7.2. There is a question about temporary tables. > How > > > can I drop a temporary table only if it exists? Thank you! > > > > Uh, just issue the drop and ignore the possible error. -- Dan Langille The FreeBSD Diary - http://freebsddiary.org/ - practical examples
----- Original Message ----- From: "David Stanaway" <David@Stanaway.net> To: "jack" <datactrl@tpg.com.au> Sent: Monday, March 25, 2002 2:17 PM Subject: Re: [SQL] About temporary table > > ----- Original Message ----- > From: jack <datactrl@tpg.com.au> > To: <pgsql-sql@postgresql.org> > Sent: Sunday, March 24, 2002 10:11 PM > Subject: Re: [SQL] About temporary table > You can see if its in the relevant system tables, then drop it. But how can we find the exact name of a temporary table? Or do you know which system table has the name mapping? JACK
jack wrote: > > ----- Original Message ----- > From: "David Stanaway" <David@Stanaway.net> > To: "jack" <datactrl@tpg.com.au> > Sent: Monday, March 25, 2002 2:17 PM > Subject: Re: [SQL] About temporary table > > > > > > ----- Original Message ----- > > From: jack <datactrl@tpg.com.au> > > To: <pgsql-sql@postgresql.org> > > Sent: Sunday, March 24, 2002 10:11 PM > > Subject: Re: [SQL] About temporary table > > > You can see if its in the relevant system tables, then drop it. > > But how can we find the exact name of a temporary table? Or do you know > which system table has the name mapping? There is actually no way to know the name from the table name, but if there is only one temp table, pg_class will have the oid of the backend in the table name. To get the backend pid, you can use libpq's PQbackendPID() function or do this: create function getpid () returns int as '/shlib/libc.so'language 'c';test=> select getpid(); getpid -------- 16011(1 row) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
To solve this problem, I think it's better to have "DROP TABLE xxx ONLY-IF-EXISTING" JACK