Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 3FB6E63F299; Mon, 20 Apr 2009 08:35:26 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 95993-01-3; Mon, 20 Apr 2009 08:35:12 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gx0-f220.google.com (mail-gx0-f220.google.com [209.85.217.220]) by mail.postgresql.org (Postfix) with ESMTP id 7B1F163F025; Mon, 20 Apr 2009 08:13:38 -0300 (ADT) Received: by mail-gx0-f220.google.com with SMTP id 20so4762714gxk.19 for ; Mon, 20 Apr 2009 04:13:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=YZu6+lE5Hj48RvisKEi/ok6f1z/ETF99XLLKq5tL6CI=; b=uRDDC00gFpqmYV3q9DTSVA0FtrkROjCpgTk2cgnsIpsmTjx+PX/K+7x7K2DEcdDP8T UF+T+dPjDGOXNTXg8iCfafYuyJZLHBbqmT2n3LRV1DDYq8wCbh2o7+y+wIhVwusNyijJ LtMUtZU1HASSuv+7U51eMVrd8XJ49rYz90ma8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=MKWGD9iEfYRUd0QtoavstL4BW5OffLvTfE8LNNyjITLVY27n5LgP9Vm/G90htZ6gvl ZjbyygqX9ZmfpL3GkqvBsqXVFKhAnrfDwEJssjm1YBQM/glcjCghs8JKC7QPtttSETRb mUZqFSx8dGqUbwp5+PYRt5YoSgfKY4k5m/CDY= MIME-Version: 1.0 Received: by 10.231.38.13 with SMTP id z13mr3260806ibd.15.1240226017460; Mon, 20 Apr 2009 04:13:37 -0700 (PDT) In-Reply-To: <41534c40904200413y60b140a4wf4305c36efd214c0@mail.gmail.com> References: <41534c40904191403m2eeabc27tc4bc6ce206c87464@mail.gmail.com> <20090419235619.GB10700@fetter.org> <41534c40904200413y60b140a4wf4305c36efd214c0@mail.gmail.com> Date: Mon, 20 Apr 2009 08:13:37 -0300 Message-ID: <41534c40904200413k19fba8d5y9ddffd61a775d573@mail.gmail.com> Subject: Re: [GENERAL] Postgresql 8.3X supports Arrays of Composite Types? From: Robson Fidalgo To: David Fetter Cc: pgsql-docs@postgresql.org, pgsql-general@postgresql.org Content-Type: multipart/alternative; boundary=00032555150a3642240467fa9fc7 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200904/42 X-Sequence-Number: 5130 --00032555150a3642240467fa9fc7 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit On Mon, Apr 20, 2009 at 8:13 AM, Robson Fidalgo wrote: > Hi David, > > > > Thanks for your help, but I want a relational-object solution. The solution > presented by Tom Lane (Thanks Tom!!!!) runs very well and it is a > relational-object implementation (I suggest put a similar example in > postgresql 8.3X documentation). > > > > Cheers, > > Robson. > > On Sun, Apr 19, 2009 at 8:56 PM, David Fetter wrote: > >> 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: david.fetter@gmail.com >> >> Remember to vote! >> Consider donating to Postgres: http://www.postgresql.org/about/donate >> >> -- >> Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-docs >> > > --00032555150a3642240467fa9fc7 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

On Mon, Apr 20, 2009 at 8:13 AM, Robson Fidalgo = <rdnf@cin.ufpe.br<= /a>> wrote:

Hi David,

=A0

Thanks for your help, but I want a relational-ob= ject solution. The solution presented by Tom Lane (Thanks Tom!!!!) runs ver= y well and it is a relational-object implementation (I suggest put a simila= r example in postgresql 8.3X documentation).

=A0

Cheers,

Robson.


On Sun, Apr 19, 2009 at 8:56 PM, David Fetter <d= avid@fetter.org> wrote:
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 exam= ple below)
> that has an attribute that is an Array of a Composite Ty= pe (ROW).
> However, I do not know how can I insert a record in this table.
You want a normalized table anyhow. =A0If you want something
deno= rmalized, use a view.

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

This = is better as:

CREATE TABLE phone (
=A0 =A0cod VARCHAR,
=A0 =A0num VARCHAR,
= =A0 =A0PRIMARY KEY(cod, num)
);

CREATE TABLE person (
=A0 =A0name varchar,
=A0 = =A0PRIMARY KEY(name)
)

CREATE TABLE person_phone (
=A0 =A0name= VARCHAR NOT NULL REFERENCES person(name),
=A0 =A0cod VARCHAR,
=A0 = =A0num VARCHAR,
=A0 =A0FOREIGN KEY(cod, num) REFERENCES phone(cod, num),
=A0 =A0PRIMARY = KEY(name, cod, num)
);

Cheers,
David.
--
David Fetter &l= t;david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 =A0AIM: dfetter666 =A0Yahoo!: dfetter
Skype: davi= dfetter =A0 =A0 =A0XMPP: david.fetter@gmail.com

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

--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make change= s to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs


--00032555150a3642240467fa9fc7--