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.
