Re: data import via COPY, Rules + Triggers

Thank your for your reply, Sergey.
(By the way, I noticed you are not cc'ing/replying to the list.)

On Fri, 6 May 2011 01:45:19 +0300
sergey kapustin <kapustin(dot)sergey(at)gmail(dot)com> wrote:

>INSERT ... RETURNING will not work in rules i think. You cannot nest
>INSERTs and its not possible to use variables.

And rules are not actioned by the COPY command (which I use to populate
the tables) whereas triggers are.

>you have
>to use conditional statements because you don't want to insert into
>manager table every time you insert new athlete.

That has been taking care of by an insert/update trigger on manager,
which updates a manager record, if necessary, where it exists already.

>I suggest you use plsql function (trigger on zathlete) instead of
>rules. Then you can do something like this -
>
>id_manager:=null
>select into id_manager id from manager where name=NEW.manager_name;
>if not found then
> insert into manager(name) values (NEW.manager_name);
> select into id_manager CURRVAL('manager_id_seq');
>end if;
>INSERT INTO athlete (... manager_fk...) VALUES ( ... id_manager...);
>
>good luck!
>
>
>On Thu, May 5, 2011 at 6:21 PM, Tarlika Elisabeth Schmitz <
>postgresql3(at)numerixtechnology(dot)de> wrote:
>
>> 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
>>
>>
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>

--

Best Regards,
Tarlika Elisabeth Schmitz