Nested custom types: array - unable to insert - Mailing list pgsql-sql

From John Poole
Subject Nested custom types: array - unable to insert
Date
Msg-id CAB74VbJ9oKeLNWxjq6FBsiibag_=gVGmvkXxtajSq2tf4brm=A@mail.gmail.com
Whole thread Raw
Responses Re: Nested custom types: array - unable to insert
List pgsql-sql
I am trying to create a custom data type for phone numbers where
I have a primary phone number and then an array of additional
phone numbers qualified by certain types.

Below is a set of SQL commands I used to set up my custom
types.  I am unable to insert into the array field and wonder
if I have found a bug, or I'm just missing some basic technique.

Here is the error I receive when trying to insert into the array field:

ERROR:  cannot cast type record[] to phonenumber_type
LINE 2: ...22222,'office'), (3333333,'cell'),(4444444,'eve')]::phonenum...

Can someone provide me an example of how to insert one or more
records into the secondary array component of the type or provide
some light on creating a custom type that would hold two fields:
1) a single type
2) an array of types  (this field may be null)


Thank you.

John Poole

=================================================
Here is the SQL to recreate my attempt:
--
-- Creating a custom data type and inserting/updating example
--
-- create database demo_typestudy1;

create type phoneattribute_type as ENUM
('home','office','day','eve','mobile','fax');


create type phonenumber_type as
(numbers int,
phone_type phoneattribute_type
);

create type contactphone_type as
(primarynumber phonenumber_type,
othernumbers phonenumber_type ARRAY
);

create table people
(id integer PRIMARY KEY,
username text NOT NULL UNIQUE,
phone contactphone_type
);

--
-- create a record w/o phone
--
insert into people
(id, username)
VALUES
(1,'bob');
--
-- add the custom data type: contactphone, just the primary and no other
-- we'll try adding array values later
--
update people
set phone = ((1234567899,'home'),NULL)
where id = 1;
--
-- also more qualified
--
update people
set phone = ROW(ROW(1234567899,'home'),NULL)
where id = 1;
--
-- most qualified (with casting)
--
update people
set phone = CAST(ROW(CAST(ROW(1234567899,'home') as phonenumber_type),NULL)as contactphone_type)
where id = 1;
--
-- view it
--
select phone
from people where id = 1;
--
-- try adding to the array field
-- replace the primary and add additional phone numbers
-- This is where things go awry.
--
update people
set phone = CAST(ROW(CAST(ROW(1111111,'home') as
phonenumber_type),ARRAY[(2222222,'office'),
(3333333,'cell'),(4444444,'eve')]::phonenumber_type)as contactphone_type)
where id = 1;


pgsql-sql by date:

Previous
From: Jan Bakuwel
Date:
Subject: Re: Current transaction is aborted, commands ignored until end of transaction block
Next
From: David Johnston
Date:
Subject: Re: Nested custom types: array - unable to insert