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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--- 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly