Curtis Scheer <Curtis@xxxxxxxxxx> writes:
> However, when I try to insert a record into foo with any other value besides
> 1 it actually inserts the record but doesn't return the # of rows affected.
Works for me:
regression=# create table foo (foovalue int);
CREATE TABLE
regression=# CREATE OR REPLACE RULE rule_foovalue AS ON INSERT TO foo
regression-# WHERE new.foovalue = 1 DO
regression-# select random();
CREATE RULE
regression=# insert into foo values(1);
random
-------------------
0.584614597726613
(1 row)
INSERT 0 1
regression=# insert into foo values(2);
random
--------
(0 rows)
INSERT 0 1
regression=#
Perhaps your client-side software is being distracted by the SELECT
result and not noticing the following INSERT result?
You might be better off casting this as a NOTHING rule to avoid that:
regression=# create function check_for_one(int) returns bool as $$
regression$# begin
regression$# raise notice 'check %', $1;
regression$# return false;
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# CREATE OR REPLACE RULE rule_foovalue AS ON INSERT TO foo
WHERE check_for_one(foovalue) DO INSTEAD NOTHING;
CREATE RULE
regression=# insert into foo values(1);
NOTICE: check 1
INSERT 0 1
regression=# insert into foo values(2);
NOTICE: check 2
INSERT 0 1
regression=#
I made my test function just throw a NOTICE so I could verify it got
called, but you could easily make it throw an error instead.
regards, tom lane
