From: Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
To: pgsql-sql@postgresql.org
Subject: data import via COPY, Rules + Triggers
Date: Wed, 4 May 2011 23:48:04 +0100
Message-ID: <20110504234804.0d0a1fff@dick.coachhouse> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: postgresql3@numerixtechnology.de
Subject: Re: data import via COPY, Rules + Triggers
In-Reply-To: <20110504234804.0d0a1fff@dick.coachhouse>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox