Re: plpgsql multidimensional array assignment results in array of text instead of subarrays

On Mon, Dec 28, 2015 at 4:05 PM, Michael Rasmussen <michaelr(at)porch(dot)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.