Re: simple rule question

On Sat, May 12, 2001 at 12:29:46AM -0400, Neil Conway wrote:
> Hi everyone,
>
> This should be easy, but for some reason I can't figure it out.
>
> I want to create a rule (or a trigger or whatever) so that when
> data is inserted into a certain table, one column of the
> newly inserted row is replaced with the result of a function.
>
> In order words:
>
> INSERT INTO foo (simple, special) VALUES ('a', 'b');
>
> Should become:
>
> INSERT INTO foo (simple, special) VALUES ('a', my_function('b'));

this code is untested, and will probably reset your partition
table. be a dupe at your own risk.

create table _real_foo (
id serial,
aFourthfield int8,
field1 int4[],
number3 float8,
secondfld char(22),
stamp timestamp default current_timestamp,
other varchar(99),
primary key(aFourthField,field1)
);

create view foo as select * from _real_foo;

create rule intercede_add as
on insert to foo
do instead
(
insert into _real_foo(
field1,
secondfld,
number3,
aFourthField
) values (
new.field1,
new.secondfld || new.field1,
somethingelse(new.number3),
somethingBasedOn(new.field1,new.aFourthField)
);
)
;

create rule intercede_edit as
on update to foo
do instead
(
update _real_foo set
field1 = new.field1,
secondfld = new.secondfld || new.field1,
number3 = somethingelse(new.number3),
somethingBasedOn(new.field1,new.aFourthField)
;
)
;

i suppose it'd be educational, or at least entertaining, to have

create rule INFINITY as
on insert to RECURSE
do instead insert into RECURSE ...

--
What do I need manners for? I already got me a wife.
-- Adam Pontipee, "Seven Brides for Seven Brothers"

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!