Re: Schema variables - new implementation for Postgres 15

On Thu, Jan 13, 2022, at 18:24, Dean Rasheed wrote:
> Those are examples that a malicious user might use, but even without
> such examples, I think it would be far too easy to inadvertently break
> a large application by defining a variable that conflicted with a
> column name you didn't know about.

I think there is also a readability problem with the non-locality of this feature.

I think it would be better to have an explicit namespace for these global variables, so that when reading code, they would stand-out.
As a bonus, that would also solve the risk of breaking code, as you pointed out.

Most code should never need any global variables at all, so in the rare occasions when they are needed, I think it's perfectly fine if some more verbose fully-qualified syntax was needed to use them, rather than to pollute the namespace and risk breaking code.

I want to bring up an idea presented earlier in a different thread:

How about exploiting reserved SQL keywords followed by a dot, as special labels?

This could solve the problem with this patch, as well as the other root label patch to access function parameters.

It's an unorthodox idea, but due to legacy, I think we need to be creative, if we want a safe solution with no risk of breaking any code, which I think should be a requirement.

Taking inspiration from Javascript, how about using the SQL reserved keyword "window"?
In Javascript, "window.variableName" means that the variable variableName declared at the global scope.

Furthermore:

"from" could be used to access function/procedure IN parameters.
"to" could be used to access function OUT parameters.
"from" or "to" could be used to access function INOUT parameters.

Examples:

SELECT u.user_id
INTO to.user_id
FROM users u
WHERE u.username = from.username;

-- After authentication, the authenticated user_id could be stored as a global variable:
window.user_id := to.user_id;

-- The authenticated user_id could then be used in queries that should filter on user_id:
SELECT o.order_id
FROM orders o
WHERE o.user_id = window.user_id;

This would require endorsement from the SQL committee of course, otherwise we would face problems if they suddenly would introduce syntax where a reserved keyword could be followed by a dot.

I think from a readability perspective, it works, since the different meanings can be distinguished by writing one in UPPERCASE and the other in lowercase.

/Joel