Thread: help with simple rule

here is the table:

                  Table "testx"
     Attribute |  Type   |      Modifier
    -----------+---------+--------------------
     starter   | integer | not null
     second    | text    | default timestamp('now')

I am trying to make a rule which states:

    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');

here is the error i am recieving:

    backend=# update testx SET starter =21910 where second = '10000000';
    ERROR:  query rewritten 10 times, may contain cycles

here is my plea for help:

    help!!! ;) seriously, i haven't been able to get this to work and
    i do not know why. any help that anyone can give me would be
    greatly appreciated.


TIA,

--ch

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!