Multidimensional Array Mapping

Snippets

Multidimensional Array Map

Works with PostgreSQL

9.1+

Written in

PL/pgSQL

Depends on

Nothing

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$;