DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:In-Reply-To:X-Apple-Yahoo-Original-Message-Folder:Mime-Version:Content-Type:Content-Transfer-Encoding:Message-Id:Cc:X-Mailer:From:X-Apple-Yahoo-Replied-Msgid:Subject:Date:To;
	b=w8OBhWoe09fCso5g0AlATszLTUi52iLc3PXP3tzImUFu2lW/x9Cy7o+PXvzsdR60mtqIeix0VyJb47QfaiDnqwsXEMR2aTBvklaoC9jjM8H3VEBOejotO4ESBEXLxeTECpME57iibeKxKk97RWk4+cqN8/hHfOLUvxvavSCaKFU=
	;
References: 
 <CAB74VbJ9oKeLNWxjq6FBsiibag_=gVGmvkXxtajSq2tf4brm=A@mail.gmail.com>
In-Reply-To: 
 <CAB74VbJ9oKeLNWxjq6FBsiibag_=gVGmvkXxtajSq2tf4brm=A@mail.gmail.com>
Mime-Version: 1.0 (iPad Mail 8J2)
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Message-Id: <F0517F61-58FF-4409-87A8-00D6F566BF27@yahoo.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
From: David Johnston <polobo@yahoo.com>
Subject: Re: Nested custom types: array - unable to insert
Date: Fri, 30 Dec 2011 22:31:34 -0500
To: John Poole <jlpoole56@gmail.com>


On Dec 30, 2011, at 21:53, John Poole <jlpoole56@gmail.com> wrote:

> 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.
>=20
> 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.
>=20
> Here is the error I receive when trying to insert into the array field:
>=20
> ERROR:  cannot cast type record[] to phonenumber_type
> LINE 2: ...22222,'office'), (3333333,'cell'),(4444444,'eve')]::phonenum...=

>=20
> 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)
>=20
>=20
> Thank you.
>=20
> John Poole
>=20
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> Here is the SQL to recreate my attempt:
> --
> -- Creating a custom data type and inserting/updating example
> --
> -- create database demo_typestudy1;
>=20
> create type phoneattribute_type as ENUM
> ('home','office','day','eve','mobile','fax');
>=20
>=20
> create type phonenumber_type as
> (numbers int,
> phone_type phoneattribute_type
> );
>=20
> create type contactphone_type as
> (primarynumber phonenumber_type,
> othernumbers phonenumber_type ARRAY
> );
>=20
> create table people
> (id integer PRIMARY KEY,
> username text NOT NULL UNIQUE,
> phone contactphone_type
> );
>=20
> --
> -- 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 =3D ((1234567899,'home'),NULL)
> where id =3D 1;
> --
> -- also more qualified
> --
> update people
> set phone =3D ROW(ROW(1234567899,'home'),NULL)
> where id =3D 1;
> --
> -- most qualified (with casting)
> --
> update people
> set phone =3D CAST(ROW(CAST(ROW(1234567899,'home') as phonenumber_type),NU=
LL)
>   as contactphone_type)
> where id =3D 1;
> --
> -- view it
> --
> select phone
> from people where id =3D 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 =3D CAST(ROW(CAST(ROW(1111111,'home') as
> phonenumber_type),ARRAY[(2222222,'office'),
> (3333333,'cell'),(4444444,'eve')]::phonenumber_type)
>   as contactphone_type)
> where id =3D 1;
>=20
>=20

... ,ARRAY[ ... ]::phonenumber_type[]

You need to cast to an array of the type which is done by adding the trailin=
g brackets to the type.

Also, your CREATE TYPE syntax (the "othernumbers phonenumber_type ARRAY" par=
t) is something I have not yet seen.  Where did you see this? I would have e=
xpected it to read "othernumbers phonenumber_type[]"; without the word ARRAY=
.

David J.