Thread: Multidimensional array definition in composite type appears parsed as string

Hi -

I am trying to declare an array of the following compound type:
CREATE TYPE myschema.mytype AS (
    sometext            text,
    onedimarray        text[],
    multidimarray        text[][]
);

The current assignment occurs as follows:
    myvar    myschema.mytype[] := ARRAY[

ROW('textaa',ARRAY['textab'],ARRAY[ARRAY['textac1','textac2']])::myschema.mytype,

ROW('textba',ARRAY['textbb'],ARRAY[ARRAY['textbc1','textbc2']])::myschema.mytype
                ];

However, each multidimarray in the assignment appears as a string on the
output from the following statement (declared within a pgSQL function):
statement: RAISE INFO '%',myvar;
outputt: INFO:
{"(textaa,{textab},\"{{textac1,textac2}}\")","(textba,{textbb},\"{{textbc1,textbc2}}\")"}

I believe that I would have expected the following output from the RAISE
INFO statement:
INFO:
{"(textaa,{textab},{{textac1,textac2}})","(textba,{textbb},{{textbc1,textbc2}})"}

I have attempted different explicit typecasts and syntax and have also tried
adding additional elements to the arrays to see if a one-element array was
the cause. So far, I have not been able to access the multidimensional array
in the composite type as an array (though I can perform string functions).
No issues were noted with the one-dimensional array. I would like to retain
a clean definition of the assignment as this variable provides a single
place for programmers to define meta-data used in subsequent dynamic SQL
calls.

Subsequent code attempts to access the multidimensional array elements using
(i,j, and k are iterators):
(myvar[i]).multidimarray[j][k];


How should I amend the assignment syntax so that subsequent code will
recognize the multidimensional arrays?

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

miller_2555 <nabble.30.miller_2555@spamgourmet.com> writes:
> I am trying to declare an array of the following compound type:
> CREATE TYPE myschema.mytype AS (
>     sometext            text,
>     onedimarray        text[],
>     multidimarray        text[][]
> );

> The current assignment occurs as follows:
>     myvar    myschema.mytype[] := ARRAY[

> ROW('textaa',ARRAY['textab'],ARRAY[ARRAY['textac1','textac2']])::myschema.mytype,

> ROW('textba',ARRAY['textbb'],ARRAY[ARRAY['textbc1','textbc2']])::myschema.mytype
>                 ];

Looks okay so far ...

> However, each multidimarray in the assignment appears as a string on the
> output from the following statement (declared within a pgSQL function):
> statement: RAISE INFO '%',myvar;
> outputt: INFO:
> {"(textaa,{textab},\"{{textac1,textac2}}\")","(textba,{textbb},\"{{textbc1,textbc2}}\")"}

> I believe that I would have expected the following output from the RAISE
> INFO statement:
> INFO:
> {"(textaa,{textab},{{textac1,textac2}})","(textba,{textbb},{{textbc1,textbc2}})"}

No, you're wrong --- that output is perfectly correct.  The
multidimarray value contains a comma, and therefore it needs to be
double-quoted according to the rules for composite type I/O.  The 1-dim
array value would have gotten quoted, too, had it contained more than
one element.

> I have attempted different explicit typecasts and syntax and have also tried
> adding additional elements to the arrays to see if a one-element array was
> the cause. So far, I have not been able to access the multidimensional array
> in the composite type as an array (though I can perform string functions).

It sounds like you are using some code that mistakenly thinks that
double quotes have a semantic meaning here.  They do not.  They are just
there to delimit members of the row value, not to tell you what type the
members are.

            regards, tom lane

Tom Lane-2 wrote:
>
> It sounds like you are using some code that mistakenly thinks that
> double quotes have a semantic meaning here.  They do not.  They are just
> there to delimit members of the row value, not to tell you what type the
> members are.
>
Note: quoted text abridged per mailing list rules.

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 had mistakenly assumed the following would be
correct:

CREATE TYPE myschema.mytype AS (
sometext text,
onedimarray text[],
multidimarray text[][]
);

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
                        FOR k IN
array_lower((myvar[i]).multidimarray[j],1)..array_upper((myvar[i]).multidimarray[j],1)
LOOP
                                RAISE INFO '%',(myvar[i]).multidimarray[j][k];
                        END LOOP;
                END LOOP;
        END LOOP;
END
$BODY$ LANGUAGE 'plpgsql';

When I attempt the above, it appears (myvar[i]).multidimarray[j] is text
instead of an array of text. Currently, (myvar[i]).multidimarray[j][k]
yields NULL in each case and (myvar[i]).multidimarray[j] yields
"{{textac1,textac2}}" and "{{textbc1,textbc2}}".

Is there an alternate/ preferred method used to access the elements?

Nearly forgot: running Postgres v8.3 on Fedora 10.

Thanks again.
--
View this message in context:
http://www.nabble.com/Multidimensional-array-definition-in-composite-type-appears-parsed-as-string-tp23749072p23750913.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Tom Lane-2 wrote:
>
> It sounds like you are using some code that mistakenly thinks that
> double quotes have a semantic meaning here.  They do not.  They are just
> there to delimit members of the row value, not to tell you what type the
> members are.
>
Note: quoted text abridged per mailing list rules.

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 had mistakenly assumed the following would be
correct:

CREATE TYPE myschema.mytype AS (
sometext text,
onedimarray text[],
multidimarray text[][]
);

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
                        FOR k IN
array_lower((myvar[i]).multidimarray[j],1)..array_upper((myvar[i]).multidimarray[j],1)
LOOP
                                RAISE INFO '%',(myvar[i]).multidimarray[j][k];
                        END LOOP;
                END LOOP;
        END LOOP;
END
$BODY$ LANGUAGE 'plpgsql';

When I attempt the above, it appears (myvar[i]).multidimarray[j] is text
instead of an array of text. Currently, (myvar[i]).multidimarray[j][k]
yields NULL in each case and (myvar[i]).multidimarray[j] yields
"{{textac1,textac2}}" and "{{textbc1,textbc2}}".

Is there an alternate/ preferred method used to access the elements?

Nearly forgot: running Postgres v8.3 on Fedora 10.

Thanks again.
--
View this message in context:
http://www.nabble.com/Multidimensional-array-definition-in-composite-type-appears-parsed-as-string-tp23749072p23750913.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

miller_2555 <nabble.30.miller_2555@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

Tom Lane-2 wrote:
>
> miller_2555 <nabble.30.miller_2555@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.

miller_2555 <nabble.30.miller_2555@spamgourmet.com> writes:
> 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.

No, it's not.  The semantics are constrained here by the fact that we
don't consider 1-D and 2-D arrays (or any-D arrays) to be distinct
types.  So the parser determines whether the result of a subscript
expression has the element type or the (same) array type based on
whether or not there's a [subscript:subscript] anywhere, not on
how many subscripts there are.

There's been some discussion of tightening things up to throw an
error rather than just returning NULL if an inappropriate number of
subscripts are given, but so far nothing's been done about it.

            regards, tom lane