Re: [DOCS] Postgresql 8.3X supports Arrays of Composite Types?

2009-04-19 Thread Robson Fidalgo 
>
> Hello,
> I am using postgresql 8.3X and I created a table (see example below) that
> has an attribute that is an Array of a Composite Type (ROW). However, I do
> not know how can I insert a record in this table.
>
> Well, I've searched the mailing lists and have found little said about the
> OR features. In postgres 8.3X on-line documentation (
> http://www.postgresql.org/docs/8.3/static/arrays.html) I found "Arrays of
> any built-in or user-defined base type, enum type, or composite type can be
> created", however there is no example that explains how can I do an insert
> into an attribute that is an Array of Composite Type. Considering the
> example below, "persons have N phones" I create (with success) the Person
> table with an array of phones, but I have not success with Insert values.
> There is some one that can help me?
>
> Thanks in advances,
>
> Robson
>
> Example:
>
> CREATE table phone (
>   cod varchar,
>   num varchar);
>
> CREATE TABLE person (
>   name varchar,
>   telephone phone[]);
>
> Until here is everything ok, but I have not success with insert values,
> then I tried:
>
> 1)insert into person values ('Joe', '{("1",""),("2","") }');
> 2)insert into person values ('Joe', array[('1',''),('2','')]);
> 3)insert into person values ('Joe',
> array[row('1',''),row('2','')]);
> 4)insert into person values ('Joe',
> _phone[phone('1',''),phone('2','')]);**
> ** considering _phone = name of array type (automatically created by
> postgres) and phone = name of composite type (also automatically created by
> postgres)
>

Re: [DOCS] [GENERAL] Postgresql 8.3X supports Arrays of Composite Types?

2009-04-19 Thread Tom Lane 
Robson Fidalgo  writes:
> I am using postgresql 8.3X and I created a table (see example below) that
> has an attribute that is an Array of a Composite Type (ROW). However, I do
> not know how can I insert a record in this table.

You need some explicit casts so that the array[] construct is seen to
have the right type, eg

insert into person values ('Joe', array[row('1','')::phone, 
row('2','')::phone]);

regards, tom lane

-- 
Sent via pgsql-docs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

Re: [DOCS] [GENERAL] Postgresql 8.3X supports Arrays of Composite Types?

2009-04-19 Thread David Fetter 
On Sun, Apr 19, 2009 at 06:03:26PM -0300, Robson Fidalgo wrote:
> Hello,
> I am using postgresql 8.3X and I created a table (see example below)
> that has an attribute that is an Array of a Composite Type (ROW).
> However, I do not know how can I insert a record in this table.

You want a normalized table anyhow.  If you want something
denormalized, use a view.

> Example:
> 
> CREATE table phone (
>   cod varchar,
>   num varchar);
> 
> CREATE TABLE person (
>   name varchar,
>   telephone phone[]);

This is better as:

CREATE TABLE phone (
cod VARCHAR,
num VARCHAR,
PRIMARY KEY(cod, num)
);

CREATE TABLE person (
name varchar,
PRIMARY KEY(name)
)

CREATE TABLE person_phone (
name VARCHAR NOT NULL REFERENCES person(name),
cod VARCHAR,
num VARCHAR,
FOREIGN KEY(cod, num) REFERENCES phone(cod, num),
PRIMARY KEY(name, cod, num)
);

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: [email protected]

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-docs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs