st 13. 11. 2024 v 17:35 odesÃlatel Dmitry Dolgov <9erthali...@gmail.com> napsal:
> > On Sun, Nov 10, 2024 at 06:51:40PM GMT, Pavel Stehule wrote: > > ne 10. 11. 2024 v 17:19 odesÃlatel Pavel Stehule < > pavel.steh...@gmail.com> > > napsal: > > I thought a lot of time about better solutions for identifier collisions > > and I really don't think so there is some consistent user friendly > syntax. > > Personally I think there is an easy already implemented solution - > > convention - just use a dedicated schema for variables and this schema > > should not be in the search path. Or use secondary convention - like > using > > prefix "__" for session variables. Common convention is using "_" for > > PLpgSQL variables. I searched how this issue is solved in other > databases, > > or in standard, and I found nothing special. The Oracle and SQL/PSM has a > > concept of visibility - the variables are not visible outside packages or > > modules, but Postgres has nothing similar. It can be emulated by a > > dedicated schema without inserting a search path, but it is less strong. > > > > I think we can introduce an alternative syntax, that will not be user > > friendly or readable friendly, but it can be without collisions - or can > > decrease possible risks. > > > > It is nothing new - SQL does it with old, "new" syntax of inner joins, or > > in Postgres we can > > > > where salary < 40000 > > > > or > > > > where pg_catalog.int4lt(salary, 40000); > > > > > > or some like we use for operators OPERATOR(*schema*.*operatorname*) > > > > So introducing VARIABLE(schema.variablename) syntax as an alternative > > syntax for accessing variables I really like. I strongly prefer to use > this > > as only alternative (secondary) syntax, because I don't think it is > > friendly syntax or writing friendly, but it is safe, and I can imagine > > tools that can replace generic syntax to this special, or that detects > > generic syntax and shows some warning. Then users can choose what they > > prefer. Two syntaxes - generic and special can be good enough for all - > and > > this can be perfectly consistent with current Postgres. > > As far as I recall, last time this topic was discussed in hackers, two > options were proposed: the one with VARIABLE(name), what you mention > here; and another one with adding variables to the FROM clause. The > VARIABLE(...) syntax didn't get much negative feedback, so I guess why > not -- if you find it fitting, it would be interesting to see the > implementation. > > I'm afraid it should not be just an alternative syntax, but the only one > allowed, because otherwise I don't see how scenarious like "drop a > column with the same name" could be avoided. As in the previous thread: > > -- we've got a variable b at the same time > SELECT a, b FROM table1; > > Then dropping the column b, but everything still works beause the > variable b got silently picked up. But if it would be required to say > VARIABLE(b), then all fine. > > And to make sure we're on the same page, could you post couple of > examples from curretly existing tests in the patch, how are they going > to look like with this proposal? > What do you think about the following design? I can implement a warning "variable_usage_guard" when the variable is accessed without using VARIABLE() syntax. We can discuss later if this warning can be enabled by default or not. There I am open to any variant. So for variable public.a and table public.foo(a, b) I can write LET a = 10; -- there is not possible collision LET a = a + 1; -- there is not possible collision, no warning SELECT a, b FROM foo; -- there is a collision - and warning "variable a is shadowed" SELECT VARIABLE(a), b FROM foo; -- no collision, no warning After ALTER TABLE foo DROP COLUMN a; SELECT a, b FROM foo; -- possible warning "the usage in variable without safe syntax", SELECT VARIABLE(a), b FROM foo; -- no warning I think this design can be good for all. variable_usage_guard can be enabled by default. If somebody uses conventions for collision protection, then he can safely disable it. Comments, notes? Regards Pavel > About adding variables to the FROM clause. Looks like this option was > quite popular, and you've mentioned some technical challenges > implementing that. If you'd like to go with another approach, it would > be great to elaborate on that -- maybe even with a PoC, to make a > convincing point here. >
