On 12/30/2011 7:31 PM, David Johnston wrote:
On Dec 30, 2011, at 21:53, John Poole<jlpool...@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. 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;... ,ARRAY[ ... ]::phonenumber_type[] You need to cast to an array of the type which is done by adding the trailing brackets to the type. Also, your CREATE TYPE syntax (the "othernumbers phonenumber_type ARRAY" part) is something I have not yet seen. Where did you see this? I would have expected it to read "othernumbers phonenumber_type[]"; without the word ARRAY. David J.
David et al., That works. demo_typestudy1=# update peopledemo_typestudy1-# set phone = CAST(ROW(CAST(ROW(1111111,'home') as phonenumber_type),ARRAY[(2222222,'office'), (3333333,'mobile'),(4444444,'eve')]::phonenumber_type[])
demo_typestudy1(# as contactphone_type) demo_typestudy1-# where id = 1; UPDATE 1 demo_typestudy1=# To answer your question, I did originally try "othernumbers phonenumber_type[]" but as I kept running into a wall, I figured I had not tried a correct syntax and somewhere found the "ARRAY" syntax as an acceptable form; I figured the more explicit, the better. It may have been from a railroad diagram. The type definition was accepted so I ran with it. Thank you, I spent several hours gated by this issue, so your insight has been a tremendous help. John
<<attachment: jlpoole56.vcf>>
-- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
