Re: plpgsql multidimensional array assignment results in array of text instead of subarrays - Mailing list pgsql-general

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?