Re: Insert Rule

Curtis Scheer <Curtis(at)DAYCOS(dot)com> 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