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
