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.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
