select a from foo; -- the "a" is ambiguous, variable "a" is shadowed
This is a basic case, and the unique names don't help. The variables are more aggressive in namespace than tables, because they don't require be in FROM clause. This is the reason why we specify so variables are always shadowed. Only this behaviour is safe and robust. I cannot break any query (that doesn't use variables) by creating any variable. On second hand, an experience from Oracle's PL/SQL or from old PLpgSQL is, so unwanted shadowing can be hard to investigate (without some tools).
PL/pgSQL doesn't allow conflict between PL/pgSQL variables, and SQL (now), and I think so it is best. But the scope of PLpgSQL variables is relatively small, so very strict behaviour is acceptable.
The session variables are some between tables and attributes. The catalog pg_class can be enhanced about columns for variables, but it does a lot now, so I think it is not practical.
I agree about shadowing schema variables. But is there no way to fix that so that you can dereference the variable? [Does an Alias work inside a procedure against a schema var?] Does adding a schema prefix resolve it properly, so your example, I could do: SELECT schema_var.a AS var_a, a as COL_A from t;
Yes, using schema can fix collisions in almost all cases. There are some possible cases, when the schema name is the same as some variable name, and in these cases there can still be collisions (and still there is a possibility to use catalog.schema.object and it can fix a collision). You can use a qualified identifier and again in most cases it fixes collisions. These cases are tested in regression tests.
Regards
Pavel
Again, I like the default that it is hidden, but I can envision needing both?
