Re: Schema variables - new implementation for Postgres 15

čt 13. 1. 2022 v 15:29 odesílatel Joel Jacobson <joel(at)compiler(dot)org> napsal:

> 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.
>

There are few absolutely valid use cases

1. scripting - currently used GUC instead session variables are slow, and
without types

2. RLS

3. Migration from Oracle - although I agree, so package variables are used
more times badly, it used there. And only in few times is possibility to
refactor code when you do migration from Oracle to Postgres, and there is
necessity to have session variables,

> 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.
>

I cannot imagine how the "window" keyword can work in SQL context. In
Javascript "window" is an object - it is not a keyword, and it makes sense
in usual Javascript context inside HTML browsers.

Regards

Pavel

>
> 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
>