多维数组映射

代码片段

多维数组映射

适用于 PostgreSQL

9.1+

使用语言

PL/pgSQL

依赖

无

这些函数演示了如何在多维数组上完成等效于 perl map 命令的操作。

函数

注意，此函数不会泛型映射，它只是展示了你可以如何实现它。

CREATE OR REPLACE FUNCTION tools.parameter_replace(
    p_templates text[]
    , p_parameters text[][]
  ) RETURNS text[] LANGUAGE plpgsql IMMUTABLE AS $body$
DECLARE
  i int;
  v_slice text[];
  v_return text[];
BEGIN
  /*
   * This function works by handling only the 1st dimension of an array. It iterates through
   * all the first dimension elements. If the array is already only 1 dimension it just calls
   * the non-array version of parameter_replace(); otherwise it calls itself with each element.
   * In that case, it will be passing itself another array, but one that has one less dimension.
   */
  RAISE DEBUG 'p_templates=%', p_templates;

  FOR i IN array_lower( p_templates, 1 ) .. array_upper( p_templates, 1 )
    LOOP

    -- If this is a one dimensional array then just call parameter_replace on each element
    IF array_ndims( p_templates ) = 1 THEN
      v_return[i] := tools.parameter_replace( p_templates[i], p_parameters );
    ELSE
      -- Grab the next slice in our array and reduce it's dimension count by one before recursing with it
      v_slice := tools.parameter_replace(
                            tools.array_reduce_dimensions( p_templates[i:i] )
                            , p_parameters
                          )
      ;

      -- We have to resort to this because v_return[i:i] generates a syntax error and v_return[i] is the wrong number of dimensions
      IF i = array_lower( p_templates, 1 ) THEN
        v_return := array[ v_slice ];
      ELSE
        v_return := v_return || v_slice;
      END IF;

      RAISE DEBUG 'i=%, v_slice=%, v_return=%', i, v_slice, v_return;
    END IF;
  END LOOP;

  RETURN v_return;
END
$body$;

这是必需的 array_reduce_dimensions 函数。请注意，你需要用你想使用的每个数组类型替换 %type%。

CREATE OR REPLACE FUNCTION tools.array_reduce_dimensions(
  , 'p_array %type%[]'
  , '%type%[]
  ) RETURNS %type%[] LANGUAGE sql IMMUTABLE AS $body$
SELECT tools.regexp_replace(
    $1::text -- Convert input array to text
    , array[
        array[ '^{', '' ] -- Remove leading {
        , array[ '}$', '' ] -- Remove trailing }
      ]
    , NULL
  )::%type%[] -- Cast back to an array
;
$body$;

最后，这是 tools.parameter_replace 的非数组版本。你只需要在你想尝试上面数组版本时使用它。

<source lang='sql'> CREATE OR REPLACE FUNCTION tools.parameter_replace(

     p_template      text
     , p_parameters  text[][]
 ) RETURNS text LANGUAGE plpgsql IMMUTABLE AS $body$

DECLARE

 v_name text := 'tools.parameter_replace';
 v_out text := p_template;

BEGIN

 /* You'd need the Enova Tools assert framework for this
 PERFORM tools.assert( p_template IS NOT NULL, 'p_template must not be NULL' );
 PERFORM tools.assert( p_parameters IS NOT NULL, 'p_parameters must not be NULL' );
 
 PERFORM tools.assert( array_lower( p_parameters, 2 ) = 1, 'Lower bound of second dimension of p_parameters must be 1' );
 PERFORM tools.assert( array_upper( p_parameters, 2 ) = 2, 'Upper bound of second dimension of p_parameters must be 2' );
 */

 FOR i IN array_lower( p_parameters, 1 ) .. array_upper( p_parameters, 1 ) LOOP
   RAISE DEBUG '%: Replacing % with %', v_name, '%' || p_parameters[i][1] || '%', p_parameters[i][2];
   v_out := replace( v_out, '%' || p_parameters[i][1] || '%', p_parameters[i][2] );
 END LOOP;

 RETURN v_out;

END; $body$;