Multiple dialects support some form of lambda expressions, mainly for array / list functions, including:

• Clickhouse: https://clickhouse.com/docs/en/sql-reference/functions#higher-order-functions---operator-and-lambdaparams-expr-function
• DuckDB: https://duckdb.org/docs/sql/functions/lambda
• Trino: https://trino.io/docs/current/functions/lambda.html

There might be others. It's not an unrealistic concept for SQL functions to be higher order functions as well, even if the specific syntax variants may appear a bit unidiomatic for standard SQL.

Also, I imagine that some array functions accepting lambdas can be emulated using UNNEST() and ARRAY_AGG(), e.g.:

-- DuckDB:
select array_filter(array[1, 2, 3, 4], e -> e > 2)

-- PostgreSQL:
select (select array_agg(e) from unnest(array[1, 2, 3, 4]) as t (e) where e > 2)

-- Alternative, with guaranteed ordering, or when ordinals are requested
select (
  select array_agg(e order by o) 
  from unnest(array[1, 2, 3, 4]) with ordinality as t (e, o)
  where e > 2
)

Tasks

• Lambda support
  • Support lambdas of degrees 1 and 2, for now
  • Add DSL API
  • Add QOM API
  • Add parser support (including all aliases)
• Array function support
  • ALL_MATCH(x[], x -> BOOLEAN) -> BOOLEAN
  • ANY_MATCH(x[], x -> BOOLEAN) -> BOOLEAN
  • NONE_MATCH(x[], x -> BOOLEAN) -> BOOLEAN
  • ARRAY_MAP(x[], x -> y) -> y[] (or TRANSFORM, etc.)
    • Refactor ARRAY_REPLACE to use this instead of plain SQL templates for DuckDB, Trino
  • ARRAY_FILTER(x[], x -> BOOLEAN) -> x[]
  • More here: Add support for more lambda accepting array functions #16596
• Kotlin extension methods would be useful, e.g. Field<T[]>.allMatch(Lambda<T, Boolean>)
• Emulations should be able to combine, e.g. ARRAY_MAP(ARRAY_FILTER(...)) should produce a single subquery, not 2: See Generate more optimal combined array lambda function emulations #16597
• Add documentation

Tests:

• Cover array literals as well as array tables (the latter will produce correlated derived tables)
• Test NULL arrays as well as empty arrays
• Test multiple array data types, including INT[], TEXT[]

Other array functions will be investigated as well, e.g. ARRAY_DISTINCT(), ARRAY_INTERSECT(), ARRAY_EXCEPT(), etc., but in a separate task.

Related tasks:

• Add Settings.transformPatterns for combining ARRAY lambda functions #16507

Notes

• The emulation may produce correlated derived tables, which aren't supported by H2, for example