Re: Schema variables - new implementation for Postgres 15

st 22. 5. 2024 v 14:37 odesílatel Peter Eisentraut <peter@eisentraut.org>
napsal:

> On 18.05.24 13:29, Alvaro Herrera wrote:
> > I want to note that when we discussed this patch series at the dev
> > meeting in FOSDEM, a sort-of conclusion was reached that we didn't want
> > schema variables at all because of the fact that creating a variable
> > would potentially change the meaning of queries by shadowing table
> > columns.  But this turns out to be incorrect: it's_variables_  that are
> > shadowed by table columns, not the other way around.
>
> But that's still bad, because seemingly unrelated schema changes can
> make variables appear and disappear.  For example, if you have
>
> SELECT a, b FROM table1
>
> and then you drop column b, maybe the above query continues to work
> because there is also a variable b.  Or maybe it now does different
> things because b is of a different type.  This all has the potential to
> be very confusing.
>

The detection of possible conflicts works well (in or outside PL too)

create variable x as int;
create table foo(x int);
insert into foo values(110);

set session_variables_ambiguity_warning to on;

(2024-05-23 08:22:34) postgres=# do $$

begin
  raise notice '%', (select x from foo);
end;
$$;
WARNING:  session variable "x" is shadowed
LINE 1: (select x from foo)
                ^
DETAIL:  Session variables can be shadowed by columns, routine's variables
and routine's arguments with the same name.
QUERY:  (select x from foo)
NOTICE:  110
DO
(2024-05-23 08:22:35) postgres=# do $$ declare x int default 100;
begin
  raise notice '%', x;
end;
$$;
WARNING:  session variable "x" is shadowed
LINE 1: x
        ^
DETAIL:  Session variables can be shadowed by columns, routine's variables
and routine's arguments with the same name.
QUERY:  x
NOTICE:  100
DO