Re: data import via COPY, Rules + Triggers

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(dot)sergey(at)gmail(dot)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(at)numerixtechnology(dot)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