Thread: Converting mysql "on update" to postgres "rule"

Thanks to everyone who's helped me before.

I'm trying to create the following mysql table in postgres:

CREATE TABLE visit (
     data1 varchar(30) NOT NULL,
     data2 varchar(30) DEFAULT NULL,
     data3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

Thus far, I've been able to get this far:
CREATE TABLE visit (
     data1 varchar(30) NOT NULL,
     data2 varchar(30) DEFAULT NULL,
     data3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE RULE timestamp_update AS ON UPDATE TO visit
     DO ALSO
     UPDATE vist
          SET data3 = CURRENT_TIMESTAMP;

I've tried multiple iterations of how to accomplish this and keep getting stuck. With the one above, postgres yells and says:
ERROR:  infinite recursion detected in rules for relation "visit"

Any ideas?

On Tue, Mar 25, 2008 at 02:51:05PM -0400, Edward Blake wrote:
> CREATE RULE timestamp_update AS ON UPDATE TO visit

Do this with a trigger instead of a rule.  I think there's an example in the
docs of something very similar, but I haven't checked just now.

A

Edward Blake escribió:
> Thanks to everyone who's helped me before.
>
> I'm trying to create the following mysql table in postgres:
>
> CREATE TABLE visit (
>      data1 varchar(30) NOT NULL,
>      data2 varchar(30) DEFAULT NULL,
>      data3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
> CURRENT_TIMESTAMP);
>
> Thus far, I've been able to get this far:
> CREATE TABLE visit (
>      data1 varchar(30) NOT NULL,
>      data2 varchar(30) DEFAULT NULL,
>      data3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP);
> CREATE RULE timestamp_update AS ON UPDATE TO visit
>      DO ALSO
>      UPDATE vist
>           SET data3 = CURRENT_TIMESTAMP;

You are in a loop...

Use a trigger that put NEW.data3 - CURRENT_TIMESTAMP
>
> I've tried multiple iterations of how to accomplish this and keep
> getting stuck. With the one above, postgres yells and says:
> ERROR:  infinite recursion detected in rules for relation "visit"
>
> Any ideas?

You're looking for a trigger, not a rule.

On Tue, 25 Mar 2008, Edward Blake wrote:

> Thanks to everyone who's helped me before.
>
> I'm trying to create the following mysql table in postgres:
>
> CREATE TABLE visit (
>     data1 varchar(30) NOT NULL,
>     data2 varchar(30) DEFAULT NULL,
>     data3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
> CURRENT_TIMESTAMP);
>
> Thus far, I've been able to get this far:
> CREATE TABLE visit (
>     data1 varchar(30) NOT NULL,
>     data2 varchar(30) DEFAULT NULL,
>     data3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP);
> CREATE RULE timestamp_update AS ON UPDATE TO visit
>     DO ALSO
>     UPDATE vist
>          SET data3 = CURRENT_TIMESTAMP;
>
> I've tried multiple iterations of how to accomplish this and keep getting
> stuck. With the one above, postgres yells and says:
> ERROR:  infinite recursion detected in rules for relation "visit"
>
> Any ideas?
>

 <snip>
|> I've tried multiple iterations of how to accomplish this and keep getting
stuck. With the one above, postgres yells and says:
|> ERROR:  infinite recursion detected in rules for relation "visit"
|>
|> Any ideas?
</snip>

Look at http://www.postgresql.org/docs/8.3/interactive/plpgsql-trigger.html