Re: Insert Rule

I'm trying to prevent any further input of a particular field value via an
insert rule with the following rule.

CREATE OR REPLACE RULE rule_foovalue AS

ON INSERT TO foo

WHERE new.foovalue = 1 DO SELECT fooexception.fooexception

FROM fooexception() fooexception(fooexception);

Basically the stored procedure that it calls raises an exception. The
behavior I wanted\excepted was to call the stored procedure when foovalue =
1 and to execute a regular insert when foovalue <> 1. What its doing is
calling the stored procedure when foovalue = 1, that part works just fine.
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.
Is there something else I need to add to the rule so that it returns the
number of rows affected like normal?

Thanks,
Curtis

On Fri, Sep 01, 2006 at 08:22:44AM -0500, Curtis Scheer wrote:
> I'm trying to prevent any further input of a particular field value via an
> insert rule with the following rule.

<snip>

> Basically the stored procedure that it calls raises an exception. The
> behavior I wanted\excepted was to call the stored procedure when foovalue =
> 1 and to execute a regular insert when foovalue <> 1. What its doing is
> calling the stored procedure when foovalue = 1, that part works just fine.
> 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.
> Is there something else I need to add to the rule so that it returns the
> number of rows affected like normal?

Rules are like macros. What's happening is that each insert query on
that table is being rewritten into two queries, one the original query,
the other your query with the condition. It's the result of the second
query that you're seeing.

It sounds like you really want a trigger, or perhaps just a CHECK
constraint.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

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