From: Shane Ambler <pgsql@007Marketing.com>
To: rodrigo.sakai@zanthus.com.br
Cc: pgsql-sql@postgresql.org
Subject: Re: Inserting data in composite types!
Date: Mon, 13 Nov 2006 23:16:14 +1030
Message-ID: <45586916.2040209@007Marketing.com> (raw)
In-Reply-To: <00fc01c70720$7364c400$4700a8c0@TREEZANTHUS>
References: <00fc01c70720$7364c400$4700a8c0@TREEZANTHUS>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pgsql@007Marketing.com, rodrigo.sakai@zanthus.com.br
Subject: Re: Inserting data in composite types!
In-Reply-To: <45586916.2040209@007Marketing.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox