Re: simple rule question - Mailing list pgsql-general

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@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!