On Mon, Dec 28, 2015 at 4:05 PM, Michael Rasmussen <micha...@porch.com> wrote:
> Hello, I’m running 9.4.5 locally on my mac doing some plpgsql development. > > I am trying to iterate through a multidimensional array using a foreach > loop, as exampled in the documentation at > http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY > . > > Here is a simplified version of the function: > > CREATE OR REPLACE FUNCTION create_table( > new_table_schema character varying, > new_table_name character varying, > create_log boolean DEFAULT true, > create_source boolean DEFAULT false > ) RETURNS void AS > $BODY$ > declare > the_tables text[][]; > the_table text[]; > begin > -- Generate array of tables to create > the_tables[1] := array[new_table_schema, new_table_name]; > > if (create_source) then > the_tables[2] := array[new_table_schema||'_source', > new_table_name||'_source']; > end if; > > RAISE NOTICE 'the_tables = %', the_tables; > > <<BIGLOOP>> > foreach the_table slice 1 in array the_tables > loop > raise notice 'schema = %; table = %', the_table[1], the_table[2]; > end loop BIGLOOP; > > end; > $BODY$ > LANGUAGE plpgsql; > > When I run it, I get the following message output: > > NOTICE: the_tables = {"{mike,test}","{mike_source,test_source}"} > > NOTICE: schema = {mike,test}; table = {mike_source,test_source} > > > I am expecting: > > NOTICE: the_tables = {{'mike','test'},{'mike_source','test_source'}} > > NOTICE: schema = mike; table = test > > NOTICE: schema = mike_source; table = test_source > > I suspect something is happening with the assignment operator :=, as those > double quotes seem to indicate the subarrays are being cast to strings? > > > I tried casting during the assignment, i.e. the_tables[1] := > array[new_table_schema, new_table_name]::text[], but that had no effect. > > > Does anyone know what I might be doing wrong? > > > I cannot adequately explain the behavior though you are likely correct that since the multi-dimensional array's type is text that the attempt to assign an array to an element converts the array to text instead of assigning the array. Two suggestions: 1) Use the array modification operators defined here: http://www.postgresql.org/docs/9.4/interactive/arrays.html#ARRAYS-MODIFYING to perform the modifications and reassign the entire result back to the variable. 2) Create a composite type which can then be a simple component of a one-dimensional array. I suggest doing both though either option might be workable alone if you wish to try things out... David J.
