Re: Means to emulate global temporary table

On Wed, Jan 11, 2017 at 7:51 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:

> On 1/11/2017 6:39 PM, Ian Lewis wrote:
>
>> Does this mean that a local temporary table created in one function in a
>> database is visible globally throughout the database for the duration of
>> the session?
>>
>
> postgres temporary tables are only visible to the session that creates
> them. all kind of wierdness would happen if they were somehow visible
> outside that session, for instance what if another session is accessing one
> of these hypothetical things, when the session that creates the temp table
> exits ? and, how do you resolve name conflicts? if session 1 creates
> temp table ABC, and session 2 creates temp table ABC, how would session 3
> know which one to use? conversely, if each session creates unique
> names, they'd have to build every sql statement from string fragments, this
> is considered poor practice, and how would session 3 know what unique name
> to use for one of these other sessions shared temporary tables? all
> very confusing.
>
>
> so I'm still not clear here what it is you expect these 'global temp
> tables' to do, and how they are supposed to behave?

​"throughout" mustn't mean "by other sessions" or this becomes unwieldy.

Here's a mock-up:

CREATE TABLE template_table ();
CREATE VIEW view_over_my_template_instance AS SELECT * FROM
my_instance_of_template_table; --fails if done here without the desired
feature

In a given session:

CREATE TEMP TABLE my_instance_of_template_table LIKE template_table;
SELECT * FROM view_over_my_template_table; -- returns only this session's
temp table data

Other sessions can simultaneously execute the same SELECT * FROM
view_over_* and get their own results.

The goal is to avoid having to CREATE TEMP TABLE within the session but
instead be able to do:

CREATE GLOBAL TEMP TABLE my_instance_of_template_table LIKE template_table;

And have the CREATE VIEW not fail and the session behavior as described.

David J.

​