Besides the array column being manipulated,
other columns can be captured as well within the lambda expression.
The following statement provides a showcase of this feature
for calculating the value of the linear function f(x) = ax + b
with transform():
Copy
Ask AI
SELECT xvalues, a, b, transform(xvalues, x -> a * x + b) as linear_function_valuesFROM ( VALUES (ARRAY[1, 2], 10, 5), (ARRAY[3, 4], 4, 2)) AS t(xvalues, a, b);
SELECT regexp_replace('once upon a time ...', '^(\w)(\w*)(\s+.*)$',x -> upper(x[1]) || x[2] || x[3]);-- Once upon a time ...
Lambda expressions can be also applied in aggregation functions.
Following statement is a sample the overly complex calculation of the sum of all elements of a column
by making use of reduce_agg():
Copy
Ask AI
SELECT reduce_agg(value, 0, (a, b) -> a + b, (a, b) -> a + b) sum_valuesFROM ( VALUES (1), (2), (3), (4), (5)) AS t(value);-- 15