From: Tarlika Elisabeth Schmitz <postgresql3@numerixtechnology.de>
To: pgsql-sql@postgresql.org
Subject: Re: data import via COPY, Rules + Triggers
Date: Sat, 7 May 2011 10:15:47 +0100
Message-ID: <20110507101547.6df1bdc5@dick.coachhouse> (raw)
In-Reply-To: <BANLkTimDyriUwKPjBmXScDSDXHarW9g-vw@mail.gmail.com>
References: <20110504234804.0d0a1fff@dick.coachhouse>
<BANLkTinPLT1qkOMZ3xanE+84=zaLVsfDLw@mail.gmail.com>
<20110505162138.0981c143@dick.coachhouse>
<BANLkTimDyriUwKPjBmXScDSDXHarW9g-vw@mail.gmail.com>
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.sergey@gmail.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@numerixtechnology.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.sergey@gmail.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@numerixtechnology.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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
--
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: <20110507101547.6df1bdc5@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