Re: help with simple rule

On Mon, Mar 12, 2001 at 03:00:14PM -0500, Chris Hayner wrote:
>     whenever someone UPDATES starter, UPDATE second with the current
>     timestamp.
>
> here is my attempt at it:
>
>     CREATE RULE testx_update AS ON UPDATE TO testx WHERE (new.starter
>     <> old.starter) DO UPDATE testx SET "second" = timestamp('now');

or,
    "hey postgres: when i try to update the table, update the
    table, okay?"

so when you try to update the table, you update the table, which
obeys the rule to update the table, using the rule to update
the table, thereby invoking the rule to update your table, which
updates yuor table, which.... boom!

here's what we've found:

make a VIEW into the table -- it could be simply

    create view XYZ as select * from TBL;

(or select only fields you need, and join to your heart's content
with other tables, if you need to) ... then

    create rule my_tweak as
    on update to XYZ -- << note: the view, not the table
    do INSTEAD update TBL -- << note: the table, not the view
        set valu = new.valu,
            stamp= timestamp('now');

and thenpick a naming scheme to differentiate your views, which
are your 'interface' to the data, from your actual tables. we use
"_tablename" for the data tables, and "tablename" for the views
(and "tablename_" for abbreviated views that help provide linking
info to other tables).

there are probably more ways to solve this, but the above works
nicely for us...

--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
        -- Isaac Asimov, 'The Genetic Code'

will@serensoft.com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!