Thread: plpgsql multidimensional array assignment results in array of text instead of subarrays
plpgsql multidimensional array assignment results in array of text instead of subarrays
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?
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
Re: plpgsql multidimensional array assignment results in array of text instead of subarrays
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$declarethe_tables text[][];the_table text[];begin-- Generate array of tables to createthe_tables[1] := array[new_table_schema, new_table_name];if (create_source) thenthe_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_tablesloopraise 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?
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.
create type textarray as (t text[]);
Re: plpgsql multidimensional array assignment results in array of text instead of subarrays
"David G. Johnston" <david.g.johnston@gmail.com> writes: > or a more semantically meaning one...the use of the inner array is > arguably a hack here meant to avoid the overhead and new type creation by > assigning meaning to array slots. Yeah, good point: it looks like Mike does not consider the columns of the array to be interchangeable at all, so really he would be better off modeling the data as you suggest. regards, tom lane
Re: plpgsql multidimensional array assignment results in array of text instead of subarrays
Thank you all for your help. I currently only have the two cases to handle, so I went with the below if-else statement which works how I expected. -- Generate array of tables to create if (create_source) then the_tables := array[[new_table_schema, new_table_name],[new_table_schema||'_source', new_table_name||'_source']]; else the_tables := array[[new_table_schema, new_table_name]]; end if; -- Michael Rasmussen Sr. Data Engineer Porch On 12/28/15, 3:51 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: >"David G. Johnston" <david.g.johnston@gmail.com> writes: >> ​or a more semantically meaning one...the use of the inner array is >> arguably a hack here meant to avoid the overhead and new type creation by >> assigning meaning to array slots. > >Yeah, good point: it looks like Mike does not consider the columns of >the array to be interchangeable at all, so really he would be better >off modeling the data as you suggest. > > regards, tom lane