Re: Schema variables - new implementation for Postgres 15

pá 31. 5. 2024 v 15:49 odesílatel Wolfgang Walther <walther(at)technowledgy(dot)de>
napsal:

> Pavel Stehule:
> > When you write RAISE NOTICE '%', x, then PLpgSQL parser rewrite it to
> > RAISE NOTICE '%', SELECT $1
> >
> > There is no parser just for expressions.
>
> That's why my suggestion in [1] already made a difference between:
>
> SELECT var;
>
> and
>
> SELECT col, var FROM table, var;
>
> So the "only require variable-in-FROM if FROM is used" should extend to
> the SQL level.
>
> That should be possible, right?
>

1. you need to implement extra path - the data from FROM clause are
processed differently than params - it is much more code (and current code
should to stay if you want to support it)

2. current default behave is implicit unpacking of composites when are used
in FROM clause. So it is problem when you want to use composite in query
without unpacking

3. when I'll support SELECT var and SELECT var FROM var together, then it
will raise a collision with self, that should be solved

4. there is not any benefit if variables and tables doen't share catalog,
but session variables requires lsn number, and it can be problem to use it
is table catalog

5. identification when the variable needs or doesn't need FROM clause isn't
easy

there can be lot of combinations like SELECT (SELECT var), c FROM tab or
SELECT var, (SELECT c) FROM c and if c is variable, then FROM is not
necessary.

If somebody will write SELECT (SELECT var OFFSET 0) FROM ... then subselect
can know nothing about outer query - so it means minimally one check over
all nodes

It is possible / but it is multiple more complex than current code (and I
am not sure if store lns in pg_class is possible ever)

6. I think so plpgsql case statement use multicolumn expression, so you can
write

CASE WHEN x = 1, (SELECT count(*) FROM tab) THEN ...

It is synthetic, but we are talking about what is possible.

and although it looks correctly, and will work if x will be plpgsql
variable, then it will not work if x will be session variable

and then you need to fix it like

CASE WHEN (SELECT x=1 FROM x), (SELECT count(*) FROM tab) THEN

so it is possible, but it is clean only in trivial cases, and can be pretty
messy

Personally, I cannot to imagine to explain to any user so following
(proposed by you) behaviour is intuitive and friendly

CREATE VARIABLE a as int;
CREATE TABLE test(id int);

SELECT a; --> ok
SELECT * FROM test WHERE id = a; -- error message "the column "a" doesn't
exists"

Best,
>
> Wolfgang
>
> [1]:
>
> https://www.postgresql.org/message-id/e7faf42f-62b8-47f4-af5c-cb8efa3e0e20%40technowledgy.de
>