From jvfingercw@wiseup.com.br Tue Oct  31 19:57:14 2006
Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id CE8D09FB3AE
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 31 Oct 2006 15:57:14 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 22837-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 31 Oct 2006 19:57:06 +0000 (UTC)
X-Greylist: delayed 00:06:38.853148 by SQLgrey-
Received: from hm32.locaweb.com.br (hm32-2.locaweb.com.br [200.234.205.174])
	by postgresql.org (Postfix) with SMTP id AD0D59FB3AF
	for <pgsql-sql@postgresql.org>; Tue, 31 Oct 2006 15:57:03 -0400 (AST)
Received: (qmail 19497 invoked from network); 31 Oct 2006 19:50:21 -0000
Received: from unknown (10.1.10.101)
	by hm32.locaweb.com.br with QMQP; 31 Oct 2006 19:50:21 -0000
Received: from unknown (HELO cf) (jvfingercw@wiseup.com.br@201.21.164.21)
	by hm101.locaweb.com.br with SMTP; 31 Oct 2006 19:50:49 -0000
Message-ID: <001901c6fd25$cc2b60d0$f001a8c0@cf>
From: "Jose" <jvfingercw@wiseup.com.br>
To: <pgsql-sql@postgresql.org>
Subject: Composite Types
Date: Tue, 31 Oct 2006 16:50:20 -0300
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0016_01C6FD0C.A6990980"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2869
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200611/62
X-Sequence-Number: 26714

This is a multi-part message in MIME format.

------=_NextPart_000_0016_01C6FD0C.A6990980
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hello
I am using  postgres r8.1 and i make 2 Composite Types
as:
CREATE TYPE regiao AS
   (pais_nome varchar(150),
    estado_nome varchar(150),
    estado_sigla varchar(4),
    cidade_nome varchar(150));
ALTER TYPE regiao OWNER TO postgres;
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
and=20
CREATE TYPE telcontato AS
   (telefone1 varchar(15),
    telefone2 varchar(15),
    telefone3 varchar(15));
ALTER TYPE telcontato OWNER TO postgres;
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
and a table as
REATE TABLE unidades
(
  unidade_pa int4 NOT NULL,
  unidade_tipo int4,
  unidade_nome varchar(100) NOT NULL,
  unidade_razao varchar(150),
  unidade_bandeira varchar(1),
  unidade_endereco varchar(150),
  unidade_bairro varchar(80),
  unidade_regiao regiao,
  unidade_cep varchar(10),
  unidade_cnpj varchar(110),
  unidade_radio varchar(15),
  unidade_contato telcontato,
  unidade_abertura date,
  unidade_contrato date,
  CONSTRAINT unidades_pkey PRIMARY KEY (unidade_pa)
)=20
WITHOUT OIDS;
ALTER TABLE unidades OWNER TO postgres;

I can insert without problems rows in my table and of corse I can =
retreave this information if I use select * from .....
ok
My problem is , How I can make a query to retreave  the cidade_nome  =
from table unidades
If I use for example
select unidade_regiao.estado_sigla from unidades
I recive this message:

ERROR:  missing FROM-clause entry for table "unidade_regiao"

If I try use
select unidade_regiao.(estado_sigla) from unidades

I recive this message

ERROR:  syntax error at or near "(" at character 23


So I ask How I can retrive a specific information if I use a Composite =
Types ?

Thaks any help
J. V. Finger



------=_NextPart_000_0016_01C6FD0C.A6990980
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2963" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hello</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>I am using&nbsp; postgres r8.1 and i =
make 2 <FONT=20
face=3D"Times New Roman">Composite Types</FONT></FONT></DIV>
<DIV><FONT size=3D2>as:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>CREATE TYPE regiao AS<BR>&nbsp;&nbsp; =
(pais_nome=20
varchar(150),<BR>&nbsp;&nbsp;&nbsp; estado_nome=20
varchar(150),<BR>&nbsp;&nbsp;&nbsp; estado_sigla=20
varchar(4),<BR>&nbsp;&nbsp;&nbsp; cidade_nome varchar(150));<BR>ALTER =
TYPE=20
regiao OWNER TO =
postgres;<BR>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</FONT=
></DIV>
<DIV><FONT face=3DArial size=3D2>and </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>CREATE TYPE telcontato =
AS<BR>&nbsp;&nbsp;=20
(telefone1 varchar(15),<BR>&nbsp;&nbsp;&nbsp; telefone2=20
varchar(15),<BR>&nbsp;&nbsp;&nbsp; telefone3 varchar(15));<BR>ALTER TYPE =

telcontato OWNER TO =
postgres;<BR>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>and a table as</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>REATE TABLE unidades<BR>(<BR>&nbsp; =
unidade_pa int4=20
NOT NULL,<BR>&nbsp; unidade_tipo int4,<BR>&nbsp; unidade_nome =
varchar(100) NOT=20
NULL,<BR>&nbsp; unidade_razao varchar(150),<BR>&nbsp; unidade_bandeira=20
varchar(1),<BR>&nbsp; unidade_endereco varchar(150),<BR>&nbsp; =
unidade_bairro=20
varchar(80),<BR>&nbsp; unidade_regiao regiao,<BR>&nbsp; unidade_cep=20
varchar(10),<BR>&nbsp; unidade_cnpj varchar(110),<BR>&nbsp; =
unidade_radio=20
varchar(15),<BR>&nbsp; unidade_contato telcontato,<BR>&nbsp; =
unidade_abertura=20
date,<BR>&nbsp; unidade_contrato date,<BR>&nbsp; CONSTRAINT =
unidades_pkey=20
PRIMARY KEY (unidade_pa)<BR>) <BR>WITHOUT OIDS;<BR>ALTER TABLE unidades =
OWNER TO=20
postgres;<BR></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>I can insert without problems rows in =
my table and=20
of corse I can retreave this information if I use select * from=20
.....</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>ok</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>My problem is , How I can make a query =
to=20
retreave&nbsp; the cidade_nome&nbsp; from table unidades</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>If I use for example</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>select unidade_regiao.estado_sigla from =

unidades</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>I recive this message:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2><BR>ERROR:&nbsp; missing FROM-clause =
entry for=20
table "unidade_regiao"</FONT></DIV><FONT face=3DArial size=3D2>
<DIV><BR>If I try use</DIV>
<DIV>select unidade_regiao.(estado_sigla) from unidades</DIV>
<DIV>&nbsp;</DIV>
<DIV>I recive this message</DIV>
<DIV><BR>ERROR:&nbsp; syntax error at or near "(" at character 23</DIV>
<DIV>&nbsp;</DIV>
<DIV><BR>So I ask How I can retrive a specific information if I use a =
Composite=20
Types ?</DIV>
<DIV>&nbsp;</DIV>
<DIV>Thaks any help</DIV>
<DIV>J. V. Finger</DIV>
<DIV>&nbsp;</DIV>
<DIV></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;</DIV></FONT></BODY></HTML>

------=_NextPart_000_0016_01C6FD0C.A6990980--




From tgl@sss.pgh.pa.us Mon Nov  6 19:18:53 2006
Received: from localhost (uranus.hub.org [200.46.204.60])
	by postgresql.org (Postfix) with ESMTP id B13F59FA103
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon,  6 Nov 2006 15:18:53 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.60]) (amavisd-new, port 10024)
	with ESMTP id 32426-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon,  6 Nov 2006 15:18:51 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id BCE189FB1EB
	for <pgsql-sql@postgresql.org>; Mon,  6 Nov 2006 15:18:51 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id kA6JIjKR028809;
	Mon, 6 Nov 2006 14:18:48 -0500 (EST)
To: "Jose" <jvfingercw@wiseup.com.br>
cc: pgsql-sql@postgresql.org
Subject: Re: Composite Types 
In-reply-to: <001901c6fd25$cc2b60d0$f001a8c0@cf> 
References: <001901c6fd25$cc2b60d0$f001a8c0@cf>
Comments: In-reply-to "Jose" <jvfingercw@wiseup.com.br>
	message dated "Tue, 31 Oct 2006 16:50:20 -0300"
Date: Mon, 06 Nov 2006 14:18:45 -0500
Message-ID: <28808.1162840725@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200611/76
X-Sequence-Number: 26728

"Jose" <jvfingercw@wiseup.com.br> writes:
> If I try use
> select unidade_regiao.(estado_sigla) from unidades

No, you should do

select (unidade_regiao).estado_sigla from unidades

			regards, tom lane



From immaad@gmail.com Mon Nov  6 19:34:19 2006
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id D72C69FA372
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon,  6 Nov 2006 15:34:19 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 26241-05 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon,  6 Nov 2006 15:34:15 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from nf-out-0910.google.com (nf-out-0910.google.com
 [64.233.182.187])
	by postgresql.org (Postfix) with ESMTP id 5B85F9FA333
	for <pgsql-sql@postgresql.org>; Mon,  6 Nov 2006 15:34:15 -0400 (AST)
Received: by nf-out-0910.google.com with SMTP id c2so829218nfe
	for <pgsql-sql@postgresql.org>; Mon, 06 Nov 2006 11:34:13 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=Wg82g15OOaItaBOhfbwp+/7L6m/DBBfna1U8+bn6YXoFcQO4gIFjAjrlUJjc35/4bLPUtFaO70i0xufcFMl1YvtN/NUN/3H5r/P+6iNl+Ek2KdiMRQTfdv2zTr1DFPfGqDLBKVGo6PpUDAcXrweIfTdEfq0AXZSIe4Va8yK9MVY=
Received: by 10.82.105.13 with SMTP id d13mr1375252buc.1162841653085;
	Mon, 06 Nov 2006 11:34:13 -0800 (PST)
Received: by 10.82.161.3 with HTTP; Mon, 6 Nov 2006 11:34:12 -0800 (PST)
Message-ID: <1f30b80c0611061134m769a4871rbb1523360fff00aa@mail.gmail.com>
Date: Tue, 7 Nov 2006 00:34:12 +0500
From: imad <immaad@gmail.com>
To: Jose <jvfingercw@wiseup.com.br>
Subject: Re: Composite Types
Cc: pgsql-sql@postgresql.org
In-Reply-To: <001901c6fd25$cc2b60d0$f001a8c0@cf>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <001901c6fd25$cc2b60d0$f001a8c0@cf>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200611/81
X-Sequence-Number: 26733

This is *not* a good practice to handle elements of a composite data
type seperately because they all build it up together and without one
of it, the data type is meaningless.

Secondly, If you are going to base our application on this principle,
you better rethink your design.

--Imad
www.EnterpriseDB.com


On 11/1/06, Jose <jvfingercw@wiseup.com.br> wrote:
>
>
> Hello
> I am using  postgres r8.1 and i make 2 Composite Types
> as:
> CREATE TYPE regiao AS
>    (pais_nome varchar(150),
>     estado_nome varchar(150),
>     estado_sigla varchar(4),
>     cidade_nome varchar(150));
> ALTER TYPE regiao OWNER TO postgres;
> ==================
> and
> CREATE TYPE telcontato AS
>    (telefone1 varchar(15),
>     telefone2 varchar(15),
>     telefone3 varchar(15));
> ALTER TYPE telcontato OWNER TO postgres;
> ===========
> and a table as
> REATE TABLE unidades
> (
>   unidade_pa int4 NOT NULL,
>   unidade_tipo int4,
>   unidade_nome varchar(100) NOT NULL,
>   unidade_razao varchar(150),
>   unidade_bandeira varchar(1),
>   unidade_endereco varchar(150),
>   unidade_bairro varchar(80),
>   unidade_regiao regiao,
>   unidade_cep varchar(10),
>   unidade_cnpj varchar(110),
>   unidade_radio varchar(15),
>   unidade_contato telcontato,
>   unidade_abertura date,
>   unidade_contrato date,
>   CONSTRAINT unidades_pkey PRIMARY KEY (unidade_pa)
> )
> WITHOUT OIDS;
> ALTER TABLE unidades OWNER TO postgres;
>
> I can insert without problems rows in my table and of corse I can retreave
> this information if I use select * from .....
> ok
> My problem is , How I can make a query to retreave  the cidade_nome  from
> table unidades
> If I use for example
> select unidade_regiao.estado_sigla from unidades
> I recive this message:
>
> ERROR:  missing FROM-clause entry for table "unidade_regiao"
>
> If I try use
> select unidade_regiao.(estado_sigla) from unidades
>
> I recive this message
>
> ERROR:  syntax error at or near "(" at character 23
>
>
> So I ask How I can retrive a specific information if I use a Composite Types
> ?
>
> Thaks any help
> J. V. Finger
>
>
>