Comment by benesch | Hacker News Reader

Comment by benesch | Hacker News Reader

Parent Full thread

No, PostgreSQL definitely lets you do this.

> An expression used inside a grouping_element can be an input column name, or the name or ordinal number of an output column (SELECT list item), or an arbitrary expression formed from input-column values. [0]

> Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values. [1]

Perhaps you are thinking of trying to use an output column name in a WHERE clause?

[0]: https://www.postgresql.org/docs/current/sql-select.html#SQL-...

[1]: https://www.postgresql.org/docs/current/sql-select.html#SQL-...

> Perhaps you are thinking of trying to use an output column name in a WHERE clause?

Yeah, I think I am thinking about this.

Its pretty frustrating because the actual query engine is perfectly capable of executing fairly complex SQL expressions efficiently (they're not really that complex computationally, but they are syntactically because of SQL's verbosity), but the code becomes quite unmaintainable if you use too many of them.

For example, the following expression:

    ROUND((
      (EXTRACT(EPOCH FROM (s.end_time - s.start_time)) / 60) -- Shift length in minutes
        - (FLOOR((EXTRACT(EPOCH FROM (s.end_time - s.start_time)) / 60) / 380) * 20) -- Break length in minutes
    )::numeric / 60 , 2) as hours_planned,

It repeats the sub-expression `EXTRACT(EPOCH FROM (s.end_time - s.start_time)) / 60)`. If I could name that sub expression and reference it multiple times then the overall expression would be a lot more readable.

Reply on news.ycombinator.com