On Wed, Jan 11, 2017 at 7:51 PM, John R Pierce <pie...@hogranch.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.
