Postgres Pro
Downloads
Home   >   mailing lists

Thread: Another question about composite types

Another question about composite types

From
"Rodrigo Sakai"
Date:
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US" style="font-size:
10.0pt;font-family:Arial">  Hi, I need to create a table like:</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-US" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><b><font face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold"> CREATE TYPE t_salary AS (</span></font></b><p
class="MsoNormal"><b><fontface="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold">   Value numeric(10,2),</span></font></b><p
class="MsoNormal"><b><fontface="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold">   Validity date</span></font></b><p
class="MsoNormal"><b><fontface="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold"> );</span></font></b><p class="MsoNormal"><b><font
face="Arial"size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold"> </span></font></b><pclass="MsoNormal"><b><font
face="Arial"size="2"><span lang="EN-US" style="font-size:10.0pt;font-family:Arial;font-weight:bold">  CREATE TABLE
employee(</span></font></b><p class="MsoNormal"><b><font face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold">    id int,</span></font></b><p class="MsoNormal"><b><font
face="Arial"size="2"><span lang="EN-US" style="font-size:10.0pt;font-family:Arial;font-weight:bold">     name
varchar(30),</span></font></b><pclass="MsoNormal"><b><font face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold">    salary t_salary[]</span></font></b><p
class="MsoNormal"><b><fontface="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold"> );</span></font></b><p class="MsoNormal"><b><font
face="Arial"size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold"> </span></font></b><pclass="MsoNormal"><b><font
face="Arial"size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial;font-weight:bold"> </span></font></b><pclass="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-US" style="font-size: 
10.0pt;font-family:Arial">  That is, I need an array of composite type and searching on google I found that is
impossibleto do it! Is it really impossible? Anyone have do it ?</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-US" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial"> Thanks!</span></font></div>

Re: Another question about composite types

From
Richard Broersma Jr
Date:
>   CREATE TYPE t_salary AS (Value numeric(10,2), Validity date);
>   CREATE TABLE employee (id int, name varchar(30), salary t_salary[]);
>
> That is, I need an array of composite type and searching on google I found
> that is impossible to do it! Is it really impossible? Anyone have do it ?

Why not use a conventional table to hold this information that references your employee table?

Regards,

Richard Broersma Jr.

RES: Another question about composite types

From
"Rodrigo Sakai"
Date:
  I know that if I create a table salary that references my employee table
works! But I'm trying to work with the composite type concept!
 Thinking in Object-Oriented Programming (OOP) I have a class employee that
is composed by the class salary among other things! Like:

Class salary { private float value; private date validity;
}

Class employee { private int ID; private String name; private salary[] sal;
}

So I'd like to map these classes to PostgreSQL. I think nested tables are
one of the advantages of object-relational databases. And it works if I
think in just one salary for each employee. But I want to keep all salary
history for each employee. Is there any way?

-----Mensagem original-----
De: Richard Broersma Jr [mailto:rabroersma@yahoo.com] 
Enviada em: segunda-feira, 13 de novembro de 2006 14:46
Para: rodrigo.sakai@zanthus.com.br; pgsql-sql@postgresql.org
Assunto: Re: [SQL] Another question about composite types

>   CREATE TYPE t_salary AS (Value numeric(10,2), Validity date);
>   CREATE TABLE employee (id int, name varchar(30), salary t_salary[]);
>
> That is, I need an array of composite type and searching on google I found
> that is impossible to do it! Is it really impossible? Anyone have do it ?

Why not use a conventional table to hold this information that references
your employee table?

Regards,

Richard Broersma Jr.

Re: Another question about composite types

From
imad
Date:
There is always an _typname created for arrays like we have _int4 for
_int4, _varchar for varchar and _timestamp for timestamp etc.

May be you have to write some _typname for your type to use it in
arrays. We always use array_in and array_out for _typenames BTW.

This may be helpful to you.


--Imad
www.EnterpriseDB.com


On 11/14/06, Rodrigo Sakai <rodrigo.sakai@zanthus.com.br> wrote:
>   I know that if I create a table salary that references my employee table
> works! But I'm trying to work with the composite type concept!
>
>   Thinking in Object-Oriented Programming (OOP) I have a class employee that
> is composed by the class salary among other things! Like:
>
> Class salary {
>   private float value;
>   private date validity;
> }
>
> Class employee {
>   private int ID;
>   private String name;
>   private salary[] sal;
> }
>
> So I'd like to map these classes to PostgreSQL. I think nested tables are
> one of the advantages of object-relational databases. And it works if I
> think in just one salary for each employee. But I want to keep all salary
> history for each employee. Is there any way?
>
> -----Mensagem original-----
> De: Richard Broersma Jr [mailto:rabroersma@yahoo.com]
> Enviada em: segunda-feira, 13 de novembro de 2006 14:46
> Para: rodrigo.sakai@zanthus.com.br; pgsql-sql@postgresql.org
> Assunto: Re: [SQL] Another question about composite types
>
> >   CREATE TYPE t_salary AS (Value numeric(10,2), Validity date);
> >   CREATE TABLE employee (id int, name varchar(30), salary t_salary[]);
> >
> > That is, I need an array of composite type and searching on google I found
> > that is impossible to do it! Is it really impossible? Anyone have do it ?
>
> Why not use a conventional table to hold this information that references
> your employee table?
>
> Regards,
>
> Richard Broersma Jr.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>