Lambda 表达式

示例

• 通过transform()函数获取数组元素的平方：

  SELECT numbers, transform(numbers, n -> n * n) as squared_numbers FROM (VALUES (ARRAY[1, 2]),(ARRAY[3, 4]),(ARRAY[5, 6, 7])) AS t(numbers);
    numbers  | squared_numbers 
  -----------|-----------------
   [1, 2]    | [1, 4]          
   [3, 4]    | [9, 16]         
   [5, 6, 7] | [25, 36, 49]    
  (3 rows)

• 利用transform()函数将数组元素转为字符串，无法转换则转为NULL输出，避免报错产生：

  SELECT transform(prices, n -> TRY_CAST(n AS VARCHAR) || '$') as price_tags FROM (VALUES (ARRAY[100, 200]),(ARRAY[30, 4])) AS t(prices);
    price_tags  
  --------------
   [100$, 200$] 
   [30$, 4$]    
  (2 rows)

• 在对数组元素进行运算时，也能获取其它列来参与运算。例如使用transform()来计算线性方程f(x) =ax + b：

  SELECT xvalues, a, b, transform(xvalues, x -> a * x + b) as linear_function_values FROM (VALUES (ARRAY[1, 2], 10, 5), (ARRAY[3, 4], 4, 2)) AS t(xvalues, a, b);
   xvalues | a  | b | linear_function_values 
  ---------|----|---|------------------------
   [1, 2]  | 10 | 5 | [15, 25]               
   [3, 4]  |  4 | 2 | [14, 18]               
  (2 rows)

• 通过any_match()过滤出至少有一个元素值大于100的数组：

  SELECT numbers FROM (VALUES (ARRAY[1,NULL,3]), (ARRAY[10,200,30]), (ARRAY[100,20,300])) AS t(numbers) WHERE any_match(numbers, n ->  COALESCE(n, 0) > 100);
      numbers     
  ----------------
   [10, 200, 30]  
   [100, 20, 300] 
  (2 rows)

• 使用regexp_replace()将首字母大写：

  SELECT regexp_replace('once upon a time ...', '^(\w)(\w*)(\s+.*)$',x -> upper(x[1]) || x[2] || x[3]); -- Once upon a time ...

• 在聚合函数中应用Lambda表达式。如使用reduce_agg()计算一个较为复杂的按列求元素和：

  SELECT reduce_agg(value, 0, (a, b) -> a + b, (a, b) -> a + b) sum_values FROM (VALUES (1), (2), (3), (4), (5)) AS t(value);
   sum_values 
  ------------
           15 
  (1 row)