Subject: how to select temp table

From: liuzg4 liuzg4 <[email-masked]>
Date: Wed, 6 May 2009 20:34:25 +0800

ver 8.4
i create two table with same name  named 'testtable'

one is temp table

 i select * from testtable
then this table is a public  or a temp ???
drop table testtable

then is this table a public or a temp? i test ,it drop temp table fisrst!

so am i  must show  public or pg_temp_1  when i has two table with same name
!!!

---

From: Robert Gravsjö <[email-masked]>
Date: Wed, 06 May 2009 19:59:04 +0200

> then this table is a public  or a temp ???

Temp. To access public use "select * from public.testtable".

"Temporary tables exist in a special schema, so a schema name cannot be 
given when creating a temporary table."

See http://www.postgresql.org/docs/8.4/static/sql-createtable.html for 
details.

/r

---

From: Alvaro Herrera <[email-masked]>
Date: Wed, 6 May 2009 15:23:54 -0400

> given when creating a temporary table."

Note that you can refer to the temp table like this:

select * from pg_temp.testtable;

Also, you can make the non-temp table first in the search path by
putting pg_temp later than the public schema (or wherever you have
created the function):

select * from testtable;	-- refers to temp
set search_path to 'public', 'pg_temp';
select * from testtable;	-- refers to non-temp

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---

From: Alvaro Herrera <[email-masked]>
Date: Wed, 6 May 2009 15:27:28 -0400

> select * from pg_temp.testtable;

BTW I found no mention of pg_temp in the doc page here:
http://www.postgresql.org/docs/current/static/ddl-schemas.html

Apparently the only mention of pg_temp is in the CREATE FUNCTION page.

---

From: David Fetter <[email-masked]>
Date: Wed, 6 May 2009 12:45:54 -0700

> Apparently the only mention of pg_temp is in the CREATE FUNCTION page.

That's probably not a good omission.  Any ideas as to where further
mentions should go?

Cheers,
David.
-- 
David Fetter <[email-masked]> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: [email-masked]

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

---

From: Tom Lane <[email-masked]>
Date: Wed, 06 May 2009 16:46:48 -0400

> Apparently the only mention of pg_temp is in the CREATE FUNCTION page.

It's also described under the search_path GUC.

			regards, tom lane

---

From: Alvaro Herrera <[email-masked]>
Date: Wed, 6 May 2009 16:53:07 -0400

> It's also described under the search_path GUC.

Hmm, we have another problem then, which is that GUC vars are not
showing in the search results when you search for their names.  For
example if I search for search_path I am suggested 
http://www.postgresql.org/docs/current/static/ddl-schemas.html#DDL-SCHEMAS-PATH
but nothing else.

Oh, I see the problem .. the index entry is "search_path configuration
parameter".  I think it would be better if it were

<primary>search_path</><secondary>configuration parameter</>

---

From: Alvaro Herrera <[email-masked]>
Date: Wed, 6 May 2009 16:58:38 -0400

> That's probably not a good omission.  Any ideas as to where further

I think mentioning it in the page I link to above would be good.  I
can't think of any further places on which it should be mentioned.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---

From: Tom Lane <[email-masked]>
Date: Wed, 06 May 2009 17:11:38 -0400

> <primary>search_path</><secondary>configuration parameter</>

Seems like what you are describing is a bug in the search engine,
not a problem we must address by doubling the vertical space needed for
literally hundreds of index entries.

			regards, tom lane

---

From: Alvaro Herrera <[email-masked]>
Date: Wed, 6 May 2009 17:19:33 -0400

> literally hundreds of index entries.

Yeah, perhaps ...  I think it should suggest all index entries that
start with the search string, not necessarily equal.

Magnus? :-)

---

From: Magnus Hagander <[email-masked]>
Date: Thu, 07 May 2009 15:44:18 +0200

> start with the search string, not necessarily equal.

Uh, I don't think that information is even available at that point. We
index only the output of the web pages, not the SGML source.

//Magnus

---

From: Alvaro Herrera <[email-masked]>
Date: Thu, 7 May 2009 11:17:32 -0400

> index only the output of the web pages, not the SGML source.

AFAIK the search "suggests" stuff from pg_docbot or something similar.
That includes index entries.

---

From: Magnus Hagander <[email-masked]>
Date: Thu, 7 May 2009 17:23:46 +0200

> That includes index entries.

The docbot index is completely manually maintained. It never reads  
anything in our documetation.

/Magnus

---

From: Alvaro Herrera <[email-masked]>
Date: Thu, 7 May 2009 11:28:14 -0400

> anything in our documetation.

So where does the "we suggest this link" data come from?

---

From: Magnus Hagander <[email-masked]>
Date: Fri, 08 May 2009 13:34:29 +0200

> So where does the "we suggest this link" data come from?

A manually maintained database. That is regularly (as in, has been once)
synced over from the IRC bot database. Which is, again, manually
maintained by ppl in the irc channel.

//Magnus