Date: Thu, 5 May 2011 16:21:38 +0100
From: Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
To: pgsql-sql@postgresql.org
Subject: Re: data import via COPY, Rules + Triggers
Message-ID: <20110505162138.0981c143@dick.coachhouse>
In-Reply-To: <BANLkTinPLT1qkOMZ3xanE+84=zaLVsfDLw@mail.gmail.com>
References: <20110504234804.0d0a1fff@dick.coachhouse>
	<BANLkTinPLT1qkOMZ3xanE+84=zaLVsfDLw@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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