Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2010
    Posts
    2

    Question Unanswered: Accessing custom type array...

    here is table structure
    Code:
    create type my_type as (
    	type_name text  
    	,values integer[]
    );
    
    create table object_list(
    	index integer primary key
    	, types my_type[]  
    );
    Insert statements
    1stEXMP)
    Code:
    insert into object_list values
    (
      1,
      CAST(ARRAY[
    	 CAST(ROW('box3d','{4,4,2}')as my_type) -- Height,Width,depth
    	,CAST(ROW('ring','{4,3}')as my_type) -- -- radius,thickness
    	,CAST(ROW('color','{255,0,0}')as my_type) -- red color R:G:B
    	] as my_type[])
      
    );
    2ndEXMP)
    Code:
    insert into object_list values
    (
      2,
      CAST(ARRAY[
    	 CAST(ROW('box3d','{4,4,2}')as my_type) -- Height,Width,depth
    	,CAST(ROW('ring','{4,3}')as my_type) -- -- radius,thickness
    	,CAST(ROW('color','{255,0,0}')as my_type) -- red color R:G:B
            ,CAST(ROW('rotX','{1,0,0}')as my_type) -- rot X side
    	] as my_type[])
    );
    in 1stEXMP there is 3 items in array and in 2ndEXMP there is 4 items in array

    my question is that how to enumerate values if i don't know how many contents are stored in an array.

    for single value i can say
    Code:
    select *,types[1].* from object_list where index=1; -- it is OK
    for dual value i can say
    Code:
    select *,types[1].*,types[2].* from object_list where index=1; -- it is not OK, it represents single row with duplicate columns.
    out put like this:
    Index|types |type_name| VALUES | type_name| VALUES
    1 | {...,...} |box3d | {4,4,2} |ring | {4,3}


    what i want is that it should show multiple row
    out put like this:

    Index|types |type_name| VALUES
    1 | {...,...} |box3d | {4,4,2}
    1 | {...,...} |ring | {4,3}


    and how to select all values from array if i don't know the length of an array, is there any iteration statement to fetch values to front language like java OR c++?
    Last edited by aliveoceans; 08-24-10 at 04:39. Reason: was missing code tag

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    I'm not sure I understand what you are after.
    Do you want to have each array element as a column in your result?

    Or do you need to have the elements of the array as rows for the same ID? If that is the case, using an array is simply wrong. Normalize your table design and all your problems go away...

    Having said that, you might get away using the unnest() function:

    Code:
    select index, unnest(types) 
    from object_list
    And plase use [code] tags when posting SQL code

  3. #3
    Join Date
    Aug 2010
    Posts
    2

    Exclamation

    Quote Originally Posted by aliveoceans View Post
    here is table structure
    Code:
    create type my_type as (
    	type_name text  
    	,values integer[]
    );
    
    create table object_list(
    	index integer primary key
    	, types my_type[]  
    );
    Insert statements
    1stEXMP)
    Code:
    insert into object_list values
    (
      1,
      CAST(ARRAY[
    	 CAST(ROW('box3d','{4,4,2}')as my_type) -- Height,Width,depth
    	,CAST(ROW('ring','{4,3}')as my_type) -- -- radius,thickness
    	,CAST(ROW('color','{255,0,0}')as my_type) -- red color R:G:B
    	] as my_type[])
      
    );
    2ndEXMP)
    Code:
    insert into object_list values
    (
      2,
      CAST(ARRAY[
    	 CAST(ROW('box3d','{4,4,2}')as my_type) -- Height,Width,depth
    	,CAST(ROW('ring','{4,3}')as my_type) -- -- radius,thickness
    	,CAST(ROW('color','{255,0,0}')as my_type) -- red color R:G:B
            ,CAST(ROW('rotX','{1,0,0}')as my_type) -- rot X side
    	] as my_type[])
    );
    in 1stEXMP there is 3 items in array and in 2ndEXMP there is 4 items in array

    my question is that how to enumerate values if i don't know how many contents are stored in an array.

    for single value i can say
    Code:
    select *,types[1].* from object_list where index=1; -- it is OK
    for dual value i can say
    Code:
    select *,types[1].*,types[2].* from object_list where index=1; -- it is not OK, it represents single row with duplicate columns.
    out put like this:
    Index|types |type_name| VALUES | type_name| VALUES
    1 | {...,...} |box3d | {4,4,2} |ring | {4,3}


    what i want is that it should show multiple row
    out put like this:

    Index|types |type_name| VALUES
    1 | {...,...} |box3d | {4,4,2}
    1 | {...,...} |ring | {4,3}


    and how to select all values from array if i don't know the length of an array, is there any iteration statement to fetch values to front language like java OR c++?

    thanks 4 reply..
    any other way.. any help to implement in java?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by aliveoceans View Post
    any other way
    What do you mean with that? Other than what?


    any help to implement in java?
    Sorry I don't use arrays or object types
    (I normalize my tables )

  5. #5
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Arrays are great to use in advanced queries and procedural code. But I'd recommend against using them in tables. Normalize them like shammat said.

    You can either use unnest() or a combo of generate_series() and array_upper()
    Here's some untested code to give you a start.
    Code:
    SELECT (ol.types[types_iter]).values[values_iter]
    FROM object_list ol
    JOIN generate_series(1, 10) types_iter ON types_iter <= array_upper(ol.types, 1)
    JOIN generate_series(1, 10) values_iter ON values_iter <= array_upper(ol.types[types_iter], 1)
    Also, make your life easier by writing some constructor functions:

    Code:
    CREATE FUNCTION my_type(text, _int) RETURNS my_type AS
    ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •