Multidimensional Array Mapping

These functions show how you can accomplish the equivalent of the perl map command on a multi-dimensional array.
== Function ==
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$
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$;
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
;
$body$;
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$
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$;
</code>
Category:PL/pgSQL