[Info-ingres] Can a rule work with a global temporary session table-

[Karl and Betty Schendel]

> On Jun 13, 2018, at 1:52 PM, nikosv <nikos.vaggalis at gmail.com> wrote:
> 
> looking at this example :
> https://github.com/fosslc/IngresTests/blob/master/be/ttpp/sep/ttpp17.sep#L146
> 
> the session table as well as the procedure calling it are defined inside the very same session.What is the point? Isn't the stored procedure supposed to live in the system independently of a session so that when called by whomever to look for the session's table existence at runtime and use it?Otherwise if  the session table has not been declared by the initiating session, the procedure gracefully die?

The procedure IS independent of the session.  It just happens to be declared in the
same session for purposes of the test.  You'll notice that the actual procedure
declaration says nothing about any session temporary table;  it simply declares its
parameter as a SET OF parameter, with a procedure-local name of "yahoo".
I could start a session, declare the DBP, commit, and end the session and it would
work just fine.

When the procedure is executed, there's a bit of jiggery-pokery that goes on which
takes the GTT parameter and plugs its table ID into a run-time area where the
stored procedure can use it.  References to "yahoo" in the DBP are compiled
indirectly, referencing the runtime table ID, and it all works.  Other sessions
could also do an "execute procedure p1(yahoo=session.my_session_table)" and
they would work as well, as long as session.my_session_table had the right
definition that matched the SET OF parameter.

You might ask why a similar mechanism wouldn't work to allow general GTT
references in a stored procedure, and the answer is that it probably would,
although it would be more complicated because there would be potentially
N session table references, and they would be harder to resolve because
they would have to live with the stored procedure instead of being part of the
execute statement.  The main reason of course is historical;  SET OF procedures
were hacked up to provide SQL '92 referential integrity support back in OpenIngres
1.2, and they implemented the minimum necessary to make it work.

Karl