Question 114366 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Why LATERAL not works with values?

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.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    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.


  2. 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;
    Login or Signup to reply.
  3. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search