Means to emulate global temporary table

From: Ian Lewis <ilewismsl(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Cc: Ian Lewis <ilewis(at)mstarlabs(dot)com>
Subject: Means to emulate global temporary table
Date: 2017-01-11 22:07:15
Message-ID: CAMoTSQ1S4T+B84448MH96xjuj4SyrR2WK0=YFOuwNrJbWre+mg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am working on porting from an SQL Anywhere server that has support for
general temporary tables. It appears that PostgreSQL does not have such
support.

We use global temporary tables as a means to provide session-local content
to clients based on calculations run on the server.

That is, the client passes functions on the server configuration
information, and the server calculates results and places those results in
known global temporary table(s). The client then obtains the results from
the global temporary table to present to the user.

The user may then manipulate the configuration and we pass that
configuration back to the server through various functions on the server.
The appropriate function then manipulates the global temporary table
results allowing the client to see the new information.

Because the tables are known, many different functions can access the same
tables during a session to manipulate the result set. And, because the
tables are global the client can see the results easily based on the
then-current table configuration on the server.

I do not see a way to emulate this kind of behavior using PostgreSQL
temporary tables. It appears that a script on the server has to create the
temporary table, or the client has to create it before calling the server
putting ownership of the table structure on the client rather than the
server where it belongs in our system.

I can easily see how to create and return a temporary table from a single
function. What I do not see is how we get multiple server-side functions to
manipulate the same temporary table.

Is there a conventional means to do something like this in PostgreSQL? If
so, is there documentation somewhere on how to manage access to temporary
tables across function calls from the client?

Ian Lewis (www.mstarlabs.com)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tomas Vondra 2017-01-11 22:30:28 Re: Are new connection/security features in order, given connection pooling?
Previous Message Stephen Frost 2017-01-11 14:10:06 Re: Are new connection/security features in order, given connection pooling?