Re: Schema variables - new implementation for Postgres 15

st 22. 5. 2024 v 14:37 odesílatel Peter Eisentraut <peter(at)eisentraut(dot)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