Thread: rule system oddity
Hi, i was doing some tests and found out this: create table foo (id serial not null primary key,name text not null ); NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" i want the view show the oid of the row it shows: create view vfoo as select oid, * from foo; so far, so good... -- then i create this rule create rule insrule as on insert to vfoo do instead insert into foo(id, name) values (new.id, new.name); and try an insert insert into vfoo values(1, 'test1'); ERROR: invalid input syntax for integer: "test1" it seems like it's trying to insert into the oid column is that the intended behaviour? or is it a bug? (i think is the latter). if it's a bug? where (in the code) is the rule expanded? regards, Jaime Casanova _________________________________________________________ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com
On Tue, Jan 04, 2005 at 11:51:54PM -0600, Jaime Casanova wrote: > create view vfoo as select oid, * from foo; > ... > create rule insrule as on insert to vfoo > do instead > insert into foo(id, name) values (new.id, new.name); > ... > insert into vfoo values(1, 'test1'); > ERROR: invalid input syntax for integer: "test1" > > it seems like it's trying to insert into the oid > column is that the intended behaviour? or is it a bug? > (i think is the latter). vfoo has three fields: oid, id, and name. If you INSERT without a column list, the values are assigned to the columns in order: oid=1, id='test1', name=NULL. Are you suggesting that the insert should ignore oid since its source is a system column? I'm not sure such behavior would be desirable because of the inconsistency it would introduce: sometimes values would be assigned to the displayed columns in order, but other times one or more of those columns might be implicitly skipped. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
--- Michael Fuhr <mike@fuhr.org> escribió: > On Tue, Jan 04, 2005 at 11:51:54PM -0600, Jaime > Casanova wrote: > > > create view vfoo as select oid, * from foo; > > ... > > create rule insrule as on insert to vfoo > > do instead > > insert into foo(id, name) values (new.id, > new.name); > > ... > > insert into vfoo values(1, 'test1'); > > ERROR: invalid input syntax for integer: "test1" > > > > it seems like it's trying to insert into the oid > > column is that the intended behaviour? or is it a > bug? > > (i think is the latter). > > vfoo has three fields: oid, id, and name. If you > INSERT without a > column list, the values are assigned to the columns > in order: oid=1, > id='test1', name=NULL. Are you suggesting that the > insert should > ignore oid since its source is a system column? I'm > not sure such > behavior would be desirable because of the > inconsistency it would > introduce: sometimes values would be assigned to the > displayed > columns in order, but other times one or more of > those columns might > be implicitly skipped. > ahhh... you are right i have to use named column list to do that insert. regards, Jaime Casanova _________________________________________________________ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com