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;