Thread: Temp table visibility

Hello all,

I was recently looking at the way "create temp table as" works and the manual specifies that temp tables given the same
nameas an existing table will be used instead of the permanent table.  It also states that the permanent table can be
accessedvia its schema-qualified name.  I may have answered my own question but is there any way to emulate this table
visibilityfunctionality when referencing tables by their schema-qualified name?  

On Fri, Aug 05, 2011 at 08:35:37AM -0400, JJ wrote:
> Hello all,
>
> I was recently looking at the way "create temp table as" works and the manual specifies that temp tables given the
samename as an existing table will be used instead of the permanent table.  It also states that the permanent table can
beaccessed via its schema-qualified name.  I may have answered my own question but is there any way to emulate this
tablevisibility functionality when referencing tables by their schema-qualified name?  
> --

No, because the schema qualification is what makes for the visibility
functionality.  To draw an analogy, suppose your $PATH includes
/usr/bin but not /usr/local/bin, and that you have two psql binaries,
one in /usr/bin and one in /usr/local/bin.  If you type psql, you get
the one in /usr/bin.  If you type /usr/local/bin/psql, you always get
that one, even though it's not in the $PATH.  The $PATH doesn't affect
the visibility of the string |psql|, just adds an implicit way of
finding such a string if it's on the path somewhere.

A

--
Andrew Sullivan
ajs@crankycanuck.ca

Andrew Sullivan <ajs@crankycanuck.ca> writes:
> On Fri, Aug 05, 2011 at 08:35:37AM -0400, JJ wrote:
>> I was recently looking at the way "create temp table as" works and the manual specifies that temp tables given the
samename as an existing table will be used instead of the permanent table.  It also states that the permanent table can
beaccessed via its schema-qualified name.  I may have answered my own question but is there any way to emulate this
tablevisibility functionality when referencing tables by their schema-qualified name?  

> No, because the schema qualification is what makes for the visibility
> functionality.

Also, you can change the priority if you have a mind to, by adding
"pg_temp" to the search_path explicitly, for example

    SET search_path = public, pg_temp;

If there's no explicit reference to pg_temp in search_path then it's
implicitly searched first, which is why unqualified references to temp
table names work the way the manual says.

            regards, tom lane

On Fri, Aug 05, 2011 at 10:55:08AM -0400, Tom Lane wrote:
> Also, you can change the priority if you have a mind to, by adding
> "pg_temp" to the search_path explicitly, for example

Hey, that's cool, and it never occurred to me.  The current text in
the 9.0 manual says this:

    Likewise, the current session's temporary-table schema,
    pg_temp_nnn, is always searched if it exists. It can be explicitly
    listed in the path by using the alias pg_temp. If it is not listed
    in the path then it is searched first (even before
    pg_catalog). However, the temporary schema is only searched for
    relation (table, view, sequence, etc) and data type names. It is
    never searched for function or operator names.

That does not actually entail that if you put pg_temp elsewhere in the
search_path, it will affect things, though I guess it's sort of
implied (I'm a dunce).  Could I suggest a patch:

    . . . then it is searched first (even before pg_catalog); this can
    be changed by explicitly listing pg_temp in the search_path.  In
    any case, the temporary schema is only searched. . .

?

A

--
Andrew Sullivan
ajs@crankycanuck.ca