Re: Schema variables - new implementation for Postgres 15 - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: Schema variables - new implementation for Postgres 15
Date
Msg-id CAFj8pRA7NFW+jcvxT5ezUGte26OX71hRjmRUPjia6vL+-QEq5w@mail.gmail.com
Whole thread Raw
In response to Re: Schema variables - new implementation for Postgres 15  (Wolfgang Walther <walther@technowledgy.de>)
List pgsql-hackers


pá 31. 5. 2024 v 15:49 odesílatel Wolfgang Walther <walther@technowledgy.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"


pgsql-hackers by date:

Previous
From: David Christensen
Date:
Subject: Re: Add memory context type to pg_backend_memory_contexts view
Next
From: Andres Freund
Date:
Subject: Re: meson "experimental"?