>
> On Wed, Jan 11, 2017 at 4:54 PM, David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>> > The way I read this is that the OP wants to be able to write functions
>> that target temporary tables. These functions all assume that said tables
>> already exist so the functions themselves do not need to be concerned with
>> their management. The OP would like to be able to define these tables as
>> persistent objects in the database catalogs but in practice they behave as
>> any other temporary table would. In effect, upon session startup, these
>> tables would be created automatically by the backend without any client
>> involvement.
>>
>
> Yes. This is more or less correct, though I am quite certain that these
> tables underlying data store structures are not defined at session startup
> on our current server. The table structures are global within the catalog
> in exactly the same sense that a normal table is. They are used to create
> the table storage when needed.
>
> > This seems a bit wasteful in terms of all those session/connections that
>> don't care a whit about said temporary tables...so maybe I'm missing
>> something here in the implementation.
>>
>
> So, there is no startup work to create the tables for a session that does
> not use the tables. While I have no information on the actual
> implementation, the actual underlying store must be created at first use,
> or something like that. But, there definitely is no more per-session cost
> to those sessions that do not use the temporary tables than the cost of
> adding any extra table to the catalog.
>
>>
>> > I don't see where "call a setup function immediately after connecting"
>> is that big a problem. The client has to declare their intent to use said
>> features - and that declaration causes normal temporary tables to spring
>> into existence. If the process functions are used without doing the first
>> step the user will get an error about relation not found. I suspect there
>> may be search_path or language limitations to this approach but the
>> complaint as written doesn't give enough detail about why our temporary
>> tables are proving insufficient.
>>
>
> 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?
>
> That is, I can define a function f_dosomething() that performs some
> operation on a relation atable that does not exist in the schema. I can
> then define the relation atable as a local temporary table in an
> initialization function, f_init(), say.
>
> Assuming I call f_init() then f_dosomething(), f_dosomething() will see
> the local temporary table defined in f_init() just as it would see any
> other table.
>
> If this is correct, it is at least a solution to the server side of what I
> am trying to replace.
>
> Once defined, is a local temporary table also visible to clients as part
> of the schema?
>
> The main remaining problem comes in the clients where we currently obtain
> the working (temporary) table structure from the global schema, which is
> nice because it means we can handle the working results exactly as we would
> handle any other table content. For example, we can define a report on the
> table and it will show whatever results we have calculated for the current
> client session. The report editor does not need to figure out how to call a
> procedure to get the table definition. As far as any application is
> concerned the global temporary table is just a table defined in the
> schema. Our current report editor has no way to define a report from a
> table that does not exist in some schema somewhere, and I am not even sure
> it is possible to get it to call a procedure before attempting to access
> the schema.
>
> This may be a big problem for us. But, I do not see any obvious work
> around for it under PostgreSQL.
>
> Ian Lewis (www.mstarlabs.com)
>