Date: Wed, 6 May 2009 15:23:54 -0400
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Robert =?iso-8859-1?Q?Gravsj=F6?= <robert@blogg.se>
Cc: liuzg4 liuzg4 <liuzg4@gmail.com>, pgsql-general@postgresql.org
Subject: Re: how to select temp table
Message-ID: <20090506192354.GK4476@alvh.no-ip.org>
References: <f97af4400905060534y6312540aqf9ad2c09b0905472@mail.gmail.com>
	<4A01CFE8.9010005@blogg.se>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <4A01CFE8.9010005@blogg.se>
User-Agent: Mutt/1.5.18 (2008-05-17)

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.