0

It not make sense, a literal is not a valid column?

SELECT x, y FROM (select 1 as x) t, LATERAL CAST(2 AS FLOAT) AS y; -- fine
SELECT x, y FROM (select 1 as x) t, LATERAL 2.0 AS y; -- SYNNTAX ERROR!

Same if you use CASE clause or x+1 expression or (x+1)... seems ERROR for any non-function.

The Pg Guide, about LATERAL expression (not LATERAL subquery), say

LATERAL is primarily useful when the cross-referenced column is necessary for computing the row(s) to be joined (...)

NOTES

The question is about LATERAL 1_column_expression not LATERAL multicolumn_subquery. Example:

SELECT x, y, exp, z
FROM (select 3) t(x), -- subquery
     LATERAL round(x*0.2+1.2) as exp,  -- expression!
     LATERAL (SELECT exp+2.0 AS y, x||'foo' as z) t2  --subquery
;

... After @klin comment showing that the Guide in another point say "only functions", the question Why? must be expressed in a more specific way, changing a litle bit the scope of the question:

Not make sense "only funcions", the syntax (x) or (x+1), encapsulatening expression in parentesis, is fine, is not?
Why only functions?

PS: perhaps there is a future plan, or perhaps a real problem on parsing generic expressions... As users we must show to PostgreSQL developers what make sense and we need.

Peter Krauss
  • 13,174
  • 24
  • 167
  • 304
  • 2
    According to [SELECT syntax](https://www.postgresql.org/docs/current/sql-select.html) there may be a subquery or a function after LATERAL. – klin Oct 02 '22 at 22:40
  • @klin you show that there is a *spec constraint*, thanks (!)... Well, maybe changing a litle bit the question, **why only functions?** What the problem with `(x)` or `(x+1)` using parentesis? – Peter Krauss Oct 02 '22 at 22:47
  • Why? Standard SQL requires a subquery. A function call in this context is a Postgres extension, very handy IMO. – klin Oct 02 '22 at 22:53
  • From a practical point of view, I don't remember needing an expression in LATERAL, while I use functions regularly (typically set-returning functions). – klin Oct 02 '22 at 23:04

3 Answers3

0

It'll all work fine if you wrap it in its own subquery

SELECT x, y FROM (select 1 as x) t, LATERAL (SELECT 2.0 AS y) z;
Blue Star
  • 1,932
  • 1
  • 10
  • 11
0

A literal is a valid value for a column, but as the docs you quoted say, LATERAL syntax is used

for computing the row(s) to be joined

A relation, such as a FROM or JOIN or LATERAL subquery clause, always computes tuples of (a single or multiple) columns. The alias you're assigning is not for an individual row, but for the whole tuple.

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
  • But "Why only functions?", the semantic of `CAST(2 AS int) AS y` and `2 AS y` is the same, why the syntactic barrier? – Peter Krauss Oct 12 '22 at 16:31
  • 1
    I guess `LATERAL 2.0 AS y` could be interpreted as `LATERAL "2"."0" AS y` (i.e. table `0` from schema `2`). The parser just is written to only accept identifiers in that location, not arbitrary expressions, to avoid any ambiguity. – Bergi Oct 12 '22 at 18:14
0

Answering "Why only functions?" by intuition.

Or "Why does the PostgreSQL spec use only functions?". Of course, it's not a question about the parser, because it complies with the specification.

The SELECT syntax Guide show the only occasions when we can use LATERAL:

[ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ] ...
[ LATERAL ] function_name ( [ argument [, ...] ] ) ...
[ LATERAL ] ROWS FROM( function_name ( [ argument [, ...] ] ) ...

So, no conflict on

[ LATERAL ] (single_expression) [ AS ] alias

The guess of @Bergi is that a literal expression like LATERAL 2.0 AS y could be interpreted as LATERAL "2"."0", the "table 0" and "schema 2"... But, as we saw above, not make sense to expect a table name after clause LATERAL, so, in fact, no ambiguity.

Conclusion: it looks like the specification of LATERAL can grow and allow the use of expressions.
This is the great advantage of being able to discuss and participate in an open community software!

Why LATERAL single_expression AS alias? Rationale:

  • to be orthogonal: any new user of PostgreSQL, that see that is valid SELECT a, x, x+b AS y FROM t, LATERAL f(a) AS x, will naturally try also expressions instead functions. It is expected in a "orthogonal system" and is intuitive for any programmer.

  • to reuse expressions: we use "chain of dependent expressions" in any language, things like a=b+c; x=a+y; z=a/2; .... It is ugly to do "SELECT(SELECT(SELECT))" in SQL, only for reuse expressions. The "chains of LATERALs" is more elegant and human-readable.
    And perhaps is better also for query optimization.

Peter Krauss
  • 13,174
  • 24
  • 167
  • 304