Postgres Pro
Downloads
Home   >   mailing lists

Thread: simple rule question

simple rule question

From
Neil Conway
Date:
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'));

(Where 'special' is the column that needs to be replaced with
the dynamically generated data, and 'my_function' is the
function I want to be called: note that my_function needs to
be passed the data it is replacing).

Would someone be kind enough to write a rule for me which does
this? All the rules I've tried to write create infinite loops
(since the RULE is defined as ON INSERT, and its action is
doing an INSERT to the same table, which triggers the rule
again).

Thanks in advance,

Neil

RE: simple rule question

From
"Andrew Snow"
Date:
Try the CREATE RULE blah AS ON INSERT TO foo DO INSTEAD form of rule
creation (note the use of the keyword INSTEAD)..

> Would someone be kind enough to write a rule for me which does
> this? All the rules I've tried to write create infinite loops
> (since the RULE is defined as ON INSERT, and its action is
> doing an INSERT to the same table, which triggers the rule
> again).


- Andrew

Re: simple rule question

From
will trillich
Date:
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!

Re: simple rule question

From
Tom Lane
Date:
Neil Conway <nconway@klamath.dyndns.org> writes:
> 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.

I don't think you can do that with a rule; I see no way to write a
non-circular rule for it.  But it's easy with a trigger.  The trigger
function body would be something like

    new.specialcol := my_function(new.specialcol);
    return new;

and then you'd declare it as a BEFORE INSERT trigger.  (AFTER INSERT
is too late to change the contents of the to-be-inserted row.)

See the manual or past discussions of triggers for the syntactical
details.

            regards, tom lane