Re: [GENERAL] Multidimensional array definition in composite type appears parsed as string -- SOLVED

[miller_2555]

Tom Lane-2 wrote:
> 
> miller_2555 <nabble.30.miller_2...@spamgourmet.com> writes:
>> I appreciate the clarification on the output. Given the assignment
>> appears
>> correct, what is the appropriate method to access the elements of the
>> multidimensional array?
> 
> I think what you're missing is the distinction between slice and simple
> element access, ie instead of this
> 
>  (myvar[i]).multidimarray[j]
> 
> you'd need something like this
> 
>  (myvar[i]).multidimarray[j][1:3]
> 
> The proposed loop coding is uselessly overcomplicated because it
> supposes that 2-D arrays could be nonrectangular.  Just use array_lower
> and array_upper on dimension 2 of the 2-D array in the innermost loop.
> 
> You might want to practice a bit with a plain 2-D array field before
> getting into the complexity of embedding it in a composite embedded
> in an array ...
> 
>                       regards, tom lane
> 

Thanks - The example is overdone, but it seemed the most illustrative. For
those who would like to see corrected code in case they run into the same
issue, I have appended a new function definition:

CREATE OR REPLACE FUNCTION myschema.mytestfunction() RETURNS void AS $BODY$
DECLARE
myvar myschema.mytype[] := ARRAY[
   
ROW('textaa',ARRAY['textab'],ARRAY[ARRAY['textac1','textac2']])::myschema.mytype,
   
ROW('textba',ARRAY['textbb'],ARRAY[ARRAY['textbc1','textbc2']])::myschema.mytype
];
BEGIN
    -- Nested loop example to output each element in multidimensional array
for each composite type
    FOR i IN array_lower(myvar,1)..array_upper(myvar,1) LOOP
        FOR j IN
array_lower((myvar[i]).multidimarray,1)..array_upper((myvar[i]).multidimarray,1)
LOOP
          /*
              OLD:
              FOR k IN
array_lower((myvar[i]).multidimarray[j],1)..array_upper((myvar[i]).multidimarray[j],1)
LOOP 
          */
          FOR k IN
array_lower((myvar[i]).multidimarray,2)..array_upper((myvar[i]).multidimarray,2)
LOOP
                RAISE INFO '%',(myvar[i]).multidimarray[j][k];
            END LOOP;
        END LOOP;
    END LOOP;
END
$BODY$ LANGUAGE 'plpgsql'; 

I had assumed that the whole slice of a sub-array would have been returned
as a 1-D array by accessing an element of the "outer" array, but that does
not appear the case. 

Thanks for the quick help and great product!
-- 
View this message in context: 
http://www.nabble.com/Multidimensional-array-definition-in-composite-type-appears-parsed-as-string-tp23749072p23764714.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general