Re: rules on INSERT can't UPDATE new instance?

Although not exactly what you were asking about, it might be easier to get
the effect with a before insert trigger written in plpgsql.

(only minimally tested -- and against a 6.5 db - and replace the 100 and 0.1
with real values)
create function checktriggerfunc() returns opaque as '
begin
if (NEW.price>100) then
NEW.price=0.1;
end if;
return NEW;
end;
' language 'plpgsql';

create trigger checktrigger before insert on bid for each row
execute procedure checktriggerfunc();

----- Original Message -----
From: "Louis-David Mitterrand" <cunctator(at)apartia(dot)ch>
To: <pgsql-general(at)hub(dot)org>
Sent: Saturday, May 20, 2000 2:00 AM
Subject: [GENERAL] rules on INSERT can't UPDATE new instance?

>
> From the create_rule man page this example is offered:
>
> CREATE RULE example_5 AS
> ON INERT TO emp WHERE new.salary > 5000
> DO
> UPDATE NEWSET SET salary = 5000;
>
> But what is "NEWSET"? Is it a keyword?
>
> My problem is that on an insert with an invalid amount I try to perform
> an update with a corrected amount, but the action part of the rule
> doesn't affect or "see" the newly inserted row (or so it seems).
>
> I tried: CREATE RULE ON INSERT TO bid WHERE new.price > limit
> DO UPDATE bid SET price = 0.1;
>
> and all price columns in the bid table would be set to 0.1 _except_ the
> newly inserted row.
>
> Am I missing something obvious?