Re: Touch row ?

On Jan 24, 2004, at 12:36 AM, Chris Travers wrote:
> I think this timestamp concept is a perfect example of where a rule is
> better.  It doesn't have to be done on a view either.

No, it doesn't, but a rule on a table can't reference the target table
in the command definition.  RULES are very much like C #define macros
-- they're placed in-line in the query plan.  They're not functions,
they don't return values; they're essentially constants that transform
all query types against the target.

Your options when using a rule on a table are limited to either doing
nothing (basically ignoring the user command -- cool for making a table
read-only), doing something against a completely separate table, or
doing a custom command against a separate table in conjunction with the
user command.


> For example:
> CREATE TABLE  mytable (
>     my_id SERIAL PRIMARY KEY,
>     last_updated TIMESTAMP);
> CREATE RULE touch_row AS ON UPDATE TO mytable DO
> (UPDATE mytable SET last_updated = NOW() WHERE my_id = NEW.my_id);

Unless your version of postgres works differently (I'm using 7.4), your
example above does *not* work:

test=# CREATE TABLE  mytable (
test(#     my_id SERIAL PRIMARY KEY,
test(#     last_updated TIMESTAMP);
NOTICE:  CREATE TABLE will create implicit sequence "mytable_my_id_seq"
for "serial" column "mytable.my_id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"mytable_pkey" for table "mytable"
CREATE TABLE
test=# CREATE RULE touch_row AS ON UPDATE TO mytable DO
test-# (UPDATE mytable SET last_updated = NOW() WHERE my_id =
NEW.my_id);
CREATE RULE
test=# insert into mytable default values;
INSERT 9950968 1
test=# update mytable set my_id = 1;
ERROR:  infinite recursion detected in rules for relation "mytable"

I might have missed something in the docs (been awhile since I've read
'em), but I don't believe a rule command can reference its target.

eric