Message-ID: <45586916.2040209@007Marketing.com>
Date: Mon, 13 Nov 2006 23:16:14 +1030
From: Shane Ambler <pgsql@007Marketing.com>
User-Agent: Thunderbird 1.5.0.7 (Macintosh/20060909)
MIME-Version: 1.0
To: rodrigo.sakai@zanthus.com.br
CC: pgsql-sql@postgresql.org
Subject: Re: Inserting data in composite types!
References: <00fc01c70720$7364c400$4700a8c0@TREEZANTHUS>
In-Reply-To: <00fc01c70720$7364c400$4700a8c0@TREEZANTHUS>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Rodrigo Sakai wrote:
>   Hi, I have a question about how to insert data in composite types!
> 
>  
> 
>   Imagine the exemple:
> 
>  
> 
> CREATE TYPE t_time AS (
> 
>   a date,
> 
>   b date
> 
> );
> 
>  
> 
> CREATE TABLE salary (
> 
>    salary numeric(10,2),
> 
>    t_date t_time
> 
> );
> 
>  
> 
> I know that if I want to insert data in the table SALARY I just have to do
> like:
> 
>  
> 
>   INSERT INTO salary VALUES (1000.00, '(2006/10/10, 2006/12/10)');
> 
>  
> 
> But if I have another table:
> 
>  
> 
> CREATE TABLE employee (
> 
>   employee_id int,
> 
>   name varchar(30),
> 
>   emp_salary salary
> 
> )

I am thinking that with the salary type here you are thinking of your 
salary table defined above?
If so and you want them in a separate table to record salary histories 
then you want to create a foreign key to link them.

You would end up with -

CREATE TABLE employee (

   employee_id int PRIMARY KEY,

   name varchar(30)

);

CREATE TABLE salary (

    emp_id int REFERENCES employee(employee_id) ON DELETE CASCADE,

    salary numeric(10,2),

    t_date t_time

);

then -
INSERT INTO salary VALUES (1, 1000.00, '(2006/10/10, 2006/12/10)');


Otherwise you will want to change the CREATE TABLE salary... to CREATE 
TYPE salary...

Probably as
CREATE TYPE salary AS(
    salary numeric(10,2),
    a date,
    b date
);

You can then
INSERT INTO employee VALUES
(1,'Hard Worker','(1000.00, 2006/10/10, 2006/12/10)');


-- 

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz