================================================== ==========================
POSTGRESQL BUG REPORT TEMPLATE
================================================== ==========================


Your name : Stephan Sachse
Your email address : sachse (at) nugmbh (dot) de


System Configuration
---------------------
Architecture (example: Intel Pentium) : AMD Athlon(tm) Processor

Operating System (example: Linux 2.4.18) : Linux 2.4.25

PostgreSQL version (example: PostgreSQL-7.4.2): PostgreSQL-7.3.4

Compiler used (example: gcc 2.95.2) : gcc 3.2


Please enter a FULL description of your problem:
------------------------------------------------
I have 3 rules an a View to INSERT into the VIEW. 1 rule without condition and
2 other rule with a condition. All INSERT querys works fine. My only problem
ist the number of affected rows for querys. some on it is always zero some it
is 1.

i try this also with v7.4.2 with absolutly the same result



Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

-- START
-- first way is for affected rows is only 1 with pid = 2
CREATE TABLE rule_test
(
id integer NOT NULL DEFAULT nextval('rule_test_id'::text),
pid integer,
f_1 varchar,
f_2 varchar
) WITH OIDS;

CREATE SEQUENCE rule_test_id;

CREATE VIEW v_rule_test AS
SELECT id AS myid, pid AS mypid, f_1 AS myf_1, f_2 AS myf_2
FROM rule_test;

CREATE RULE v_rule_test_ins AS ON INSERT TO v_rule_test
DO INSTEAD
NOTHING;

CREATE RULE v_rule_test_ins_p1 AS ON INSERT TO v_rule_test WHERE new.mypid=1
DO INSTEAD
INSERT INTO rule_test (pid, f_1, f_2) VALUES (1, NEW.myf_1, NEW.myf_2);

CREATE RULE v_rule_test_ins_p2 AS ON INSERT TO v_rule_test WHERE new.mypid=2
DO INSTEAD
INSERT INTO rule_test (pid, f_1, f_2) VALUES (1, NEW.myf_2, NEW.myf_1);

INSERT INTO v_rule_test (mypid, myf_1, myf_2) VALUES (1, 'field 1', 'field 2');
INSERT INTO v_rule_test (mypid, myf_1, myf_2) VALUES (2, 'field 1', 'field 2');
INSERT INTO v_rule_test (mypid, myf_1, myf_2) VALUES (11, 'field 1', 'field 2');

-- output truncate
-- INSERT 0 0
-- INSERT 34588 1
-- INSERT 0 0

-- ENDE


-- START
-- second way is for affected rows always zero
-- all same as above except the Rules are not INSTEAD

CREATE TABLE rule_test
(
id integer NOT NULL DEFAULT nextval('rule_test_id'::text),
pid integer,
f_1 varchar,
f_2 varchar
) WITH OIDS;

CREATE SEQUENCE rule_test_id;

CREATE VIEW v_rule_test AS
SELECT id AS myid, pid AS mypid, f_1 AS myf_1, f_2 AS myf_2
FROM rule_test;

CREATE RULE v_rule_test_ins AS ON INSERT TO v_rule_test
DO INSTEAD
NOTHING;

CREATE RULE v_rule_test_ins_p1 AS ON INSERT TO v_rule_test WHERE new.mypid=1
DO
INSERT INTO rule_test (pid, f_1, f_2) VALUES (1, NEW.myf_1, NEW.myf_2);

CREATE RULE v_rule_test_ins_p2 AS ON INSERT TO v_rule_test WHERE new.mypid=2
DO
INSERT INTO rule_test (pid, f_1, f_2) VALUES (1, NEW.myf_2, NEW.myf_1);

INSERT INTO v_rule_test (mypid, myf_1, myf_2) VALUES (1, 'field 1', 'field 2');
INSERT INTO v_rule_test (mypid, myf_1, myf_2) VALUES (2, 'field 1', 'field 2');
INSERT INTO v_rule_test (mypid, myf_1, myf_2) VALUES (11, 'field 1', 'field 2');
-- output truncate
-- INSERT 0 0
-- INSERT 0 0
-- INSERT 0 0

-- ENDE



If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings