Re: Insert Rule

Curtis Scheer <Curtis@DAYCOS.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