On 01/11/2017 02:07 PM, Ian Lewis wrote:
> 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.
So what makes them temporary as they seem to persist between sessions?
>
> 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.
That can be handled with SECURITY DEFINER:
https://www.postgresql.org/docs/9.6/static/sql-createfunction.html
"EXTERNAL] SECURITY INVOKER
[EXTERNAL] SECURITY DEFINER
SECURITY INVOKER indicates that the function is to be executed with
the privileges of the user that calls it. That is the default. SECURITY
DEFINER specifies that the function is to be executed with the
privileges of the user that created it.
The key word EXTERNAL is allowed for SQL conformance, but it is
optional since, unlike in SQL, this feature applies to all functions not
only external ones.
"
>
> 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 <http://www.mstarlabs.com>)
--
Adrian Klaver
adrian.klaver@aklaver.com