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$declarethe_tables text[][];the_table text[];begin-- Generate array of tables to createthe_tables[1] := array[new_table_schema, new_table_name];if (create_source) thenthe_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_tablesloopraise 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:
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.
