Re: Schema variables - new implementation for Postgres 15

>
>
>> example
>>
>> create variable a as int;
>> create table foo(a int);
>>
>> 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?
>
> Regards, Kirk
>