Thread: Working with Array of Composite Type

Working with Array of Composite Type

From
Alex Magnum
Date:
Hello,
I am struggling with finding the right way to deal with arrays of composite types. Bellow is an example of the general setup where I defined an image type to describe the image properties. A user can have mulitple images stored.

What is the best way to:
  
  a) search for an image within the array
     e.g.  WHERE image.id = 3
           WHERE is_private IS TRUE

  b) to update an image inside the array.
     e.g. is_private = FALSE WHERE image.id = 2

  c) to delete an image why its id
     e.g. WHERE image.id=2

  d) to create a listing like
     in the unset, but with the field names of the type
 
e.g.
user_id | id | caption         | is_primary | is_private 
    ---------+----+-----------------+------------+-------------
           1 | 1  | This is Image A | f          | f
           1 | 2  | This is Image B | f          | f

CREATE TYPE image AS (
  id                smallint,
  caption           text,
  is_primary        boolean,
  is_private        boolean
);

CREATE TABLE users (
  user_id           serial NOT NULL,
  curr_count        smallint,            -- just an image identifier
  images            image[]
);


-- create the initial user record
INSERT INTO users VALUES (default,0,null);

-- inserting new elements
UPDATE users SET curr_count=curr_count+1, images=images || ARRAY[row(curr_count+1,'This is Image A',false,false)::image] WHERE user_id=1;
UPDATE users SET curr_count=curr_count+1, images=images || ARRAY[row(curr_count+1,'This is Image B',false,false)::image] WHERE user_id=1;
UPDATE users SET curr_count=curr_count+1, images=images || ARRAY[row(curr_count+1,'This is Image C',false,true)::image]  WHERE user_id=1;
UPDATE users SET curr_count=curr_count+1, images=images || ARRAY[row(curr_count+1,'This is Image D',true,false)::image]  WHERE user_id=1;

-- list the images
SELECT user_id,curr_count,unnest(images) as limages from users WHERE user_id=1;

SELECT user_id,curr_count,unnest(images) as limages from users WHERE user_id=1;
 user_id | curr_count |          limages
---------+------------+---------------------------
       1 |          4 | (1,"This is Image A",f,f)
       1 |          4 | (2,"This is Image B",f,f)
       1 |          4 | (3,"This is Image C",f,t)
       1 |          4 | (4,"This is Image D",t,f)

Any help or suggestion on this topic is highly appreciated.

Thanks
Alex

Re: Working with Array of Composite Type

From
Jan de Visser
Date:
On March 28, 2015 06:18:49 PM Alex Magnum wrote:
> Hello,
> I am struggling with finding the right way to deal with arrays of composite
> types. Bellow is an example of the general setup where I defined an image
> type to describe the image properties. A user can have mulitple images
> stored.

The canonical answer is that in almost all cases where you think you want an
array of composites, you *really* want a table join:

i.e. turn your image *type* into an image *table* with the user_id as a
foreign key.

CREATE TABLE users (
  user_id           serial NOT NULL,
);

CREATE TABLE image (
  id                smallint,
  user_id     int references users (user_id)
  caption           text,
  is_primary        boolean,
  is_private        boolean
);




Re: Working with Array of Composite Type

From
Jim Nasby
Date:
On 3/28/15 9:36 AM, Jan de Visser wrote:
> On March 28, 2015 06:18:49 PM Alex Magnum wrote:
>> Hello,
>> I am struggling with finding the right way to deal with arrays of composite
>> types. Bellow is an example of the general setup where I defined an image
>> type to describe the image properties. A user can have mulitple images
>> stored.
>
> The canonical answer is that in almost all cases where you think you want an
> array of composites, you *really* want a table join:
>
> i.e. turn your image *type* into an image *table* with the user_id as a
> foreign key.
>
> CREATE TABLE users (
>    user_id           serial NOT NULL,
> );
>
> CREATE TABLE image (
>    id                smallint,
>    user_id     int references users (user_id)
>    caption           text,
>    is_primary        boolean,
>    is_private        boolean
> );

Another option is to use unnest() to turn the array into a recordset,
which you can then use SQL on. If the array is quite small you might get
away with that. But if you're actually storing images you'll probably be
pretty unhappy with performance, because every time you make ANY change
to that array you'll need to completely re-write the *entire* array to disk.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com