Nested custom types: array - unable to insert

From: John Poole <jlpoole56(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Nested custom types: array - unable to insert
Date: 2011-12-31 02:53:38
Message-ID: CAB74VbJ9oKeLNWxjq6FBsiibag_=gVGmvkXxtajSq2tf4brm=A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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;

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message David Johnston 2011-12-31 03:31:34 Re: Nested custom types: array - unable to insert
Previous Message Jan Bakuwel 2011-12-31 02:30:34 Re: Current transaction is aborted, commands ignored until end of transaction block