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"