how to select temp table

On 2009-05-06 14.34, liuzg4 liuzg4 wrote:

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 ???

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

Robert Gravsj� escribi�:

On 2009-05-06 14.34, liuzg4 liuzg4 wrote:

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 ???

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."

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.

Alvaro Herrera escribi�:

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

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.

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

On Wed, May 06, 2009 at 03:27:28PM -0400, Alvaro Herrera wrote:

Alvaro Herrera escribi�:

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

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.

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

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

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

Alvaro Herrera <alvherre@commandprompt.com> writes:

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

Tom Lane escribi�:

Alvaro Herrera <alvherre@commandprompt.com> writes:

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

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</>

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

David Fetter escribi�:

On Wed, May 06, 2009 at 03:27:28PM -0400, Alvaro Herrera wrote:

Alvaro Herrera escribi�:

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

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.

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

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

Alvaro Herrera <alvherre@commandprompt.com> writes:

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</>

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

Tom Lane escribi�:

Alvaro Herrera <alvherre@commandprompt.com> writes:

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</>

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.

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

Magnus? :-)

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

Alvaro Herrera wrote:

Tom Lane escribi�:

Alvaro Herrera <alvherre@commandprompt.com> writes:

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</>

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.

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

Magnus? :-)

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

Magnus Hagander escribi�:

Alvaro Herrera wrote:

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

Magnus? :-)

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.

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

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

On 7 maj 2009, at 17.17, Alvaro Herrera <alvherre@commandprompt.com>
wrote:

Magnus Hagander escribió:

Alvaro Herrera wrote:

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

Magnus? :-)

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.

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

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

/Magnus

Magnus Hagander escribi�:

On 7 maj 2009, at 17.17, Alvaro Herrera <alvherre@commandprompt.com>
wrote:

Magnus Hagander escribi�:

Alvaro Herrera wrote:

Yeah, perhaps ... I think it should suggest all index entries that
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.

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

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

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

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

Alvaro Herrera wrote:

Magnus Hagander escribi�:

On 7 maj 2009, at 17.17, Alvaro Herrera <alvherre@commandprompt.com>
wrote:

Magnus Hagander escribi�:

Alvaro Herrera wrote:

Yeah, perhaps ... I think it should suggest all index entries that
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.

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

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

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