Re: Means to emulate global temporary table

> On Jan 11, 2017, at 7:02 PM, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> 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