From: Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
To: pgsql-sql@postgresql.org
Subject: Re: data import via COPY, Rules + Triggers
Date: Thu, 5 May 2011 16:21:38 +0100
Message-ID: <20110505162138.0981c143@dick.coachhouse> (raw)
In-Reply-To: <BANLkTinPLT1qkOMZ3xanE+84=zaLVsfDLw@mail.gmail.com>
References: <20110504234804.0d0a1fff@dick.coachhouse>
<BANLkTinPLT1qkOMZ3xanE+84=zaLVsfDLw@mail.gmail.com>
Thank you for your help, Sergey. That certainly works.
I was wondering whether the manager.id could maybe be obtained via
INSERT ... RETURNING?
--
Best Regards,
Tarlika Elisabeth Schmitz
On Thu, 5 May 2011 08:45:32 +0300
sergey kapustin <kapustin.sergey@gmail.com> wrote:
>Try using (select id from manager where name=NEW.manager_name) to get
>the newly inserted manager.
>The "name" column in "manager" table should have unique constraint -
>this will be good both for performance and consistency.
>
>
>
>CREATE OR REPLACE RULE zathlete_insert_1 AS
> ON INSERT TO zathlete
> DO ALSO
> (
> INSERT INTO athlete
> (id, name, _received) VALUES
> (NEW.dad_id, NEW.dad_name, NEW._received);
> INSERT INTO sponsor
> (id, name, _received) VALUES
> (NEW.sponsor_id, NEW.sponsor_name, NEW._received);
> INSERT INTO manager
> (name, _received) VALUES
> (NEW.manager_name, NEW._received);
> INSERT INTO athlete
> (id, name, dad_fk, sponsor_fk, manager_fk, _received) VALUES
> (NEW.id, NEW.name, NEW.dad_id,
> NEW.sponsor_id, (select id from manager where
>name=NEW.manager_name), NEW._received);
>)
>;
>
>On Thu, May 5, 2011 at 1:48 AM, Tarlika Elisabeth Schmitz <
>postgresql3@numerixtechnology.de> wrote:
>
>> [...]
>>
>>
>> I created interim tables matching the structure of the CSV formats
>> (about 6 of them). I want to import via COPY and distribute the data
>> to the "proper" tables via rules + triggers.
>>
>> I just hit a wall with one of the rules, (see example below): how do
>> I populate athlete.manager_fk, which is the result of the previous
>> INSERT?
>>
>>
>>
>>
>>
>> -- interim table
>> CREATE TABLE zathlete
>> (
>> id integer NOT NULL,
>> "name" character varying(50) NOT NULL,
>> dad_id integer,
>> dad_name character varying(50),
>> sponsor_id integer,
>> sponsor_name character varying(50),
>> manager_name character varying(50),
>> _received timestamp without time zone NOT NULL
>> )
>>
>> -- proper tables
>> CREATE TABLE sponsor
>> (
>> id integer NOT NULL,
>> "name" character varying(50) NOT NULL,
>> _received timestamp without time zone NOT NULL,
>> CONSTRAINT sponsor_pkey PRIMARY KEY (id)
>> )
>>
>> CREATE TABLE manager
>> (
>> id serial NOT NULL,
>> "name" character varying(50) NOT NULL,
>> _received timestamp without time zone NOT NULL,
>> CONSTRAINT manager_pkey PRIMARY KEY (id)
>> )
>>
>> CREATE TABLE athlete
>> (
>> id integer NOT NULL,
>> "name" character varying(50) NOT NULL,
>> dad_fk integer,
>> sponsor_fk integer,
>> manager_fk integer,
>> _received timestamp without time zone NOT NULL,
>> CONSTRAINT athlete_pkey PRIMARY KEY (id),
>> CONSTRAINT manager_athlete_fk FOREIGN KEY (manager_fk)
>> REFERENCES manager (id) MATCH SIMPLE
>> ON UPDATE CASCADE ON DELETE RESTRICT,
>> CONSTRAINT sponsor_athlete_fk FOREIGN KEY (sponsor_fk)
>> REFERENCES sponsor (id) MATCH SIMPLE
>> ON UPDATE CASCADE ON DELETE RESTRICT,
>> CONSTRAINT dad_athlete_fk FOREIGN KEY (dad_fk)
>> REFERENCES athlete (id) MATCH SIMPLE
>> ON UPDATE CASCADE ON DELETE RESTRICT
>> )
>>
>>
>> -- rules
>>
>> CREATE OR REPLACE RULE zathlete_insert_1 AS
>> ON INSERT TO zathlete
>> DO ALSO -- INSTEAD once all is working
>> (
>> INSERT INTO athlete
>> (id, name, _received) VALUES
>> (NEW.dad_id, NEW.dad_name, NEW._received);
>> INSERT INTO sponsor
>> (id, name, _received) VALUES
>> (NEW.sponsor_id, NEW.sponsor_name, NEW._received);
>> INSERT INTO manager
>> (name, _received) VALUES
>> (NEW.manager_name, NEW._received);
>> INSERT INTO athlete
>> (id, name, dad_fk, sponsor_fk, manager_fk, _received)
>> VALUES (NEW.id, NEW.name, NEW.dad_id,
>> NEW.sponsor_id, ?????, NEW._received);
>> )
>>
>>
>>
>>
>>
>> ====
>> System: PostgreSQL 8.3
>> no of users: 1
>>
>> --
>>
>> Best Regards,
>> Tarlika Elisabeth Schmitz
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: postgresql3@numerixtechnology.de
Subject: Re: data import via COPY, Rules + Triggers
In-Reply-To: <20110505162138.0981c143@dick.coachhouse>
* 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