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
