There is a private 'number_hired' which given its name I thought was counting the number of employee, but it was just counting the number of "hire_employee" calls in the current session... Not very interesting.
I am sure, so package variables are not shared between sessions/backends
Indeed, I misinterpreted the Oracle documentation example.
[ grantable function example to access a private session variable... ]
I am sorry, it is not secure. Theoretically it can work if you have granted order of function calls, but if not?
I'm not sure I understand.
If you do not grant/revoke permissions as you want on the functions, then it can be invoked by anybody.
My point is that it is *possible* to tune permissions so as to control exactly who may access a private session variable.
That is exactly the same with a grantable session variable if you do not have done the necessary grant/revoke, there is no difference?
If you use pattern DECLARE IF NOT EXISTS, you cannot be sure so some other did it. It is working only if you create variables in session as first.
Only if object is fixed in schema, then object is trustworthy - because you have to have rights to modify schema. In my proposal only trustworthy user can create the variable in some schema. Not trustworthy user can use public schema, or we can support temporary objects (similar to your proposal) in hypothetical schema "private". I have strong tools in Postgres for enforcing security, and I would to use it.
