Re: [GENERAL] Means to emulate global temporary table

> On Jan 11, 2017, at 7:02 PM, David G. Johnston  wrote:
> 
> ​"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.

Would this differ in any user-visible way from what you'd have if you executed at the start of each session:

CREATE TEMPORARY TABLE my_instance_of_template_table LIKE template_table;
CREATE TEMPORARY VIEW view_over_my_template_instance AS SELECT * FROM my_instance_of_template_table;

There'd be a small amount of session startup overhead, but that could be handled at the pooler level and amortized down to zero.

Cheers,
  Steve