> # Juergen.Rose@xxxxxxxxxx / 2005-07-22 09:10:01 +0200:
> > > # Juergen.Rose@xxxxxxxxxx / 2005-07-21 19:11:04 +0200:
> > > > I use some updateable views to handle my data (which
> are amazingly
> > > > slow), which gives me ultimate flexibility to handle my data.
> > > >
> > > > there are some insert rules which use currval() to get the last
> > > > sequence id for my data which I have to insert.
> > > >
> > > > The problem now is, it works fine if I do the statement
> via normal
> > > > insert into satatements, even within a transaction
> block. So far so
> > > > good. But If I migrate my old data via **Insert into my_new_view
> > > > Select ... From my_old_table**, Only the last retrieved
> value of the
> > > > sequences is used which blows my whole internal logic, because
> > > > obviously I want to use the current (for that row) and
> not the last
> > > > id.
>
> > create table olddata(
> > id int,
> > lastname varchar(50),
> > firstname varchar(50)
> > );
> >
> > insert into olddata values (1, 'Picard', 'Jean Luc');
> > insert into olddata values (3, 'Spock', 'Harold');
> > insert into olddata values (6, 'Zimmerman', 'Doc');
> > insert into olddata values (9, 'Lefler', 'Robin');
> > insert into olddata values (10, 'Kirk', 'James T.');
> >
> > create table neworg(
> > orgid serial,
> > legacyid int,
> > orgname varchar(100)
> > );
> >
> > create table newpersons(
> > persid serial,
> > orgid int,
> > lastname varchar(50),
> > firstname varchar(50)
> > );
> >
> > create view v_persons as
> > select
> > P.orgid,
> > O.legacyid,
> > P.persid,
> > P.lastname,
> > P.firstname
> > from
> > neworg O,
> > newpersons P
> > where
> > O.orgid = P.orgid;
> >
> > create or replace rule r_insert_a_organisation as on insert
> to v_persons
> > do instead
> > insert into neworg(
> > orgid,
> > legacyid,
> > orgname)
> > values (
> > coalesce(new.orgid, nextval('neworg_orgid_seq')),
> > new.legacyid,
> > coalesce(new.lastname, '') || ', ' ||
> coalesce(new.firstname,
> > '')
> > );
> >
> > create or replace rule r_insert_b_persons as on insert to v_persons
> > do
> > insert into newpersons(
> > orgid,
> > lastname,
> > firstname)
> > values (
> > coalesce(new.orgid, currval('neworg_orgid_seq')),
> > new.lastname,
> > new.firstname
> > );
>
> > /* test 3 */
> > insert into v_persons(legacyid, lastname, firstname) select * from
> > olddata;
> > select * from v_persons;
> >
> > /* my result:
> >
> > orgid | legacyid | persid | lastname | firstname
> > -------+----------+--------+-----------+-----------
> > 1 | 11 | 1 | Dax | Jadzia
> > 2 | 12 | 2 | Bashir | Dr.
> > 3 | 13 | 3 | | Odo
> > 4 | 14 | 4 | | Worf
> > 9 | 10 | 5 | Picard | Jean Luc
> > 9 | 10 | 6 | Spock | Harold
> > 9 | 10 | 7 | Zimmerman | Doc
> > 9 | 10 | 8 | Lefler | Robin
> > 9 | 10 | 9 | Kirk | James T.
> > (9 rows)
> >
> > */
> >
> > And exactly in test 3 you see my problem, it should
> actually look like
> >
> > orgid | legacyid | persid | lastname | firstname
> > -------+----------+--------+-----------+-----------
> > 1 | 11 | 1 | Dax | Jadzia
> > 2 | 12 | 2 | Bashir | Dr.
> > 3 | 13 | 3 | | Odo
> > 4 | 14 | 4 | | Worf
> > 5 | 1 | 5 | Picard | Jean Luc
> > 6 | 3 | 6 | Spock | Harold
> > 7 | 6 | 7 | Zimmerman | Doc
> > 8 | 9 | 8 | Lefler | Robin
> > 9 | 10 | 9 | Kirk | James T.
> >
> > Why the heck gets the wrong data inserted if it is an int!!!???
> >
> > I hope somebody will help me out on this, for me this looks
> very much
> > like a bug.
>
> PostgreSQL did exactly what you told it to do. RULEs *rewrite
> queries*, which means the INSERT INTO ... SELECT gets
> transformed to
> something like
>
> insert into neworg( orgid, legacyid, orgname)
> select
> coalesce(new.orgid, nextval('neworg_orgid_seq')),
> new.id as legacyid,
> coalesce(new.lastname, '') || ', ' ||
> coalesce(new.firstname, '')
> from olddata new;
>
> insert into newpersons ( orgid, lastname, firstname)
> select
> coalesce(new.orgid, currval('neworg_orgid_seq')),
> new.lastname,
> new.firstname
> from olddata new;
>
> and this is run once, not for every row. IOW, you'll have this
> problem with any multi-row inserts.
So I can't actually solve this problem, but what I could do would be to
not create views, but tables with rules, and put some trigger on the
tables?
Further if I understand you right, the rules are transformed actually to
two different queries which are executed one after another and not row
by row?
Thanks for the enlightment so far
Juergen
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
