From: | Alex Magnum <magnum11200(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Working with Array of Composite Type |
Date: | 2015-03-28 12:48:49 |
Message-ID: | CA+cR4zeXqs0K_uCeGPe2RNP7JQ8zY=kUP7yhfZk-La=8_JZhcA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jan de Visser | 2015-03-28 14:36:37 | Re: Working with Array of Composite Type |
Previous Message | Murthy Sandeep | 2015-03-28 12:10:10 | Homebrew installation question for PostgreSQL 9.4.1 (OS X 10.10.2) |