[GENERAL] plpgsql multidimensional array assignment results in array of text instead of subarrays

[Michael Rasmussen]

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?


--
Michael Rasmussen
Sr. Data Engineer
Porch