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

Michael Rasmussen <michaelr@porch.com> writes:
> 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;

That's not going to work, because it's a type violation.
Multi-dimensional arrays in PG are not arrays of arrays.
(Maybe they should have been, but it's too late to change that.)
The only reason you don't get an immediate runtime error is that
plpgsql is so lax about type coercions, and a text value will
accept pretty much anything.

The right way to do what you're trying to do is array slice assignment.
Ideally, you'd write the above like this:

    -- Generate array of tables to create
    the_tables[1:1][1:2] := array[new_table_schema, new_table_name];

    if (create_source) then
        the_tables[2:2][1:2] := array[new_table_schema||'_source', new_table_name||'_source'];
    end if;

Unfortunately, that's got two problems: no one's ever gotten around to
making array slice assignment syntax work at all in plpgsql, and even if
it did, the second assignment requires extension of an already-existing
array value, which we don't currently support for multi-D cases (that's
the core array code's fault not plpgsql's fault).

Both of those things could probably be made to happen if anyone cared
to put in the work, but that won't help you in existing releases.

A workaround I've seen used is to create a composite type, so that
what you have is 1-D arrays of composite types of 1-D arrays:

create type textarray as (t text[]);

CREATE OR REPLACE FUNCTION create_table(
    new_table_schema character varying,
    new_table_name character varying,
    create_source boolean
) RETURNS void AS
$BODY$
declare
    the_tables textarray[];
    the_table textarray;
begin
    -- Generate array of tables to create
    the_tables[1] := row(array[new_table_schema, new_table_name])::textarray;

    if (create_source) then
        the_tables[2] := row(array[new_table_schema||'_source', new_table_name||'_source'])::textarray;
    end if;

    RAISE NOTICE 'the_tables = %', the_tables;

    <<BIGLOOP>>
    foreach the_table in array the_tables
    loop
        raise notice 'schema = %; table = %', the_table.t[1], the_table.t[2];
    end loop BIGLOOP;

end;
$BODY$
  LANGUAGE plpgsql;

select create_table('mike', 'test', true);

            regards, tom lane