Working with Array of Composite Type

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

Responses

Browse pgsql-general by date

  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)