[BUGS] RULE ON INSERT and DEFAULT nextval('...')

[falcon]

Hello pgsql-bugs,

Excuse my English. I'm from Russia.
I understood, that following is implementation behavior,
but this is really not what i'm expecting to see.

--Start test--
create table try1
(
id serial PRIMARY KEY,-- same with DEFAULT nextval('some_sequence')
info varchar(30)
)
without oids;

create table handle_try1
(
id_try1 int PRIMARY KEY,
inf int NOT NULL DEFAULT 0
)
without oids;

create rule try1_insert as
on insert to try1 do insert into handle_try1 (id_try1)
  values (new.id);

insert into try1(info) values ('hello');
insert into try1(id,info) values(3,'hell'); -- later one'll see the reason for 
id=3

select * from try1;
/*
returns
 id | info
+---
  1 | hello
  3 | hell
(2 rows)
*/
select * from handle_try1;
/*
returns
 id_try1 | inf
-+---
   2 |   0
   3 |   0
(2 rows)
but I really expected
 id_try1 | inf
-+---
   1 |   0
   3 |   0
(2 rows)
*/
-- Finish Test

Cause of this I ought to use trigger, what I really do not want to do.
And, i think, same behavior takes the place with other volatile function 
defaults.

mailto:[EMAIL PROTECTED]



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[BUGS] Repeat RULE ON INSERT and DEFAULT nextval('...')

[falcon]

Hello pgsql-bugs,

Excuse my English. I'm from Russia.
I realized i forgotten to write version of PostgreSQL in previous letter.
I'm using PostgreSQL 8.0.1 on Windows and Slackware 10.0 compilled with gcc 
3.3.4

I understood, that following is implementation behavior,
but this is really not what i'm expecting to see.

--Start test--
create table try1
(
id serial PRIMARY KEY,-- same with DEFAULT nextval('some_sequence')
info varchar(30)
)
without oids;

create table handle_try1
(
id_try1 int PRIMARY KEY,
inf int NOT NULL DEFAULT 0
)
without oids;

create rule try1_insert as
on insert to try1 do insert into handle_try1 (id_try1)
  values (new.id);

insert into try1(info) values ('hello');
insert into try1(id,info) values(3,'hell'); -- later one'll see the reason for 
id=3

select * from try1;
/*
returns
 id | info
+---
  1 | hello
  3 | hell
(2 rows)
*/
select * from handle_try1;
/*
returns
 id_try1 | inf
-+---
   2 |   0
   3 |   0
(2 rows)
but I really expected
 id_try1 | inf
-+---
   1 |   0
   3 |   0
(2 rows)
*/
-- Finish Test

Cause of this I ought to use trigger, what I really do not want to do.
And, i think, same behavior takes the place with other volatile function 
defaults.

mailto:[EMAIL PROTECTED]



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[BUGS] RULE ON INSERT and INSERT INTO ... SELECT

[falcon]

Hello pgsql-bugs,

I'm here again. I'm using PostgreSQL 8.0.1 on Windows and Linux Slackware 10.0

--Start test--
create sequence try3_seq;
create table try3
(
id int PRIMARY KEY,
info varchar(30)
)
without oids;
create table try4
(
id int,
info varchar(30)
)
without oids;

create table handle_try3
(
id serial PRIMARY KEY,
info varchar(30) NOT NULL DEFAULT 0
)
without oids;

-- /*
create rule try4_insert as
on insert to try4 do instead
(
insert into try3(id,info) values (nextval('try3_seq'),new.info);
insert into handle_try3(info) vales (new.info);
 );
-- */
/* -- same result with
create rule try4_insert as
on insert to try4 do instead
insert into try3(id,info) values (nextval('try3_seq'),new.info);
create rule try4_insert_also
on insert to try4 do
insert into handle_try3(info) values (new.info);
*/
insert into try4(info) values ('hello'); -- simple inserts works well
insert into try4(info) values ('hell');

insert into try4(info) select info from try3;

select * from handle_try3;
/*
returns
 id|info
---+---
1  |hello
2  |hell
3  |hello
4  |hell
5  |hello
6  |hell

but it seems to me, there must be only 4 rows.
*/
-- Finish Test

mailto:[EMAIL PROTECTED]



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])