Multidimensional Array Mapping

From PostgreSQL wiki
Jump to navigationJump to search


Multidimensional Array Map

Works with PostgreSQL


Written in


Depends on


These functions show how you can accomplish the equivalent of the perl map command on a multi-dimensional array.


Note that this function doesn't generically map, it just shows you could could do it.

CREATE OR REPLACE FUNCTION tools.parameter_replace(
    p_templates text[]
    , p_parameters text[][]
  ) RETURNS text[] LANGUAGE plpgsql IMMUTABLE AS $body$
  i int;
  v_slice text[];
  v_return text[];
   * 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 )

    -- 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 );
      -- 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 ];
        v_return := v_return || v_slice;
      END IF;

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

  RETURN v_return;

This is the required array_reduce_dimensions function. Note that you will need to replace %type% with every array type you want to use this with.

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

Finally, this is the non-array version of tools.parameter_replace. You only need this if you want to try the array version above as-is.

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

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


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


 /* 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] );
 RETURN v_out;

END; $body$;