Thread: affected rows from INSERT INTO view with rules and conditions

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