Michael Rasmussen <micha...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general