Date: Wed, 4 May 2011 23:48:04 +0100
From: Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
To: pgsql-sql@postgresql.org
Subject: data import via COPY, Rules + Triggers
Message-ID: <20110504234804.0d0a1fff@dick.coachhouse>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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_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