data import via COPY, Rules + Triggers
От | Tarlika Elisabeth Schmitz |
---|---|
Тема | data import via COPY, Rules + Triggers |
Дата | |
Msg-id | 20110504234804.0d0a1fff@dick.coachhouse обсуждение исходный текст |
Ответы |
Re: data import via COPY, Rules + Triggers
(Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>)
|
Список | pgsql-sql |
I have got a database that needs to be populated, first with historical data, then on a daily basis. Number of entities is small, data volume huge. Once imported, data will neither be modified nor deleted. Data come in denormalized CSV formats. There are overlaps between different CSV formats and existing data will keep on reappearing and need updating to catch possible changes. 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_idinteger, sponsor_name character varying(50), manager_name character varying(50), _received timestamp without timezone NOT NULL ) -- proper tables CREATE TABLE sponsor ( id integer NOT NULL, "name" character varying(50) NOT NULL, _received timestamp without time zone NOT NULL, CONSTRAINTsponsor_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_pkeyPRIMARY KEY (id) ) CREATE TABLE athlete ( id integer NOT NULL, "name" character varying(50) NOT NULL, dad_fk integer, sponsor_fk integer, manager_fk integer, _receivedtimestamp without time zone NOT NULL, CONSTRAINT athlete_pkey PRIMARY KEY (id), CONSTRAINT manager_athlete_fk FOREIGNKEY (manager_fk) REFERENCES manager (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT sponsor_athlete_fkFOREIGN 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 ONDELETE RESTRICT ) -- rules CREATE OR REPLACE RULE zathlete_insert_1 AS ON INSERT TO zathlete DO ALSO -- INSTEAD once all is working (INSERT INTOathlete (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
В списке pgsql-sql по дате отправления: