pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Adding a rule to update a last_changed field
7+ messages / 4 participants
[nested] [flat]

* Adding a rule to update a last_changed field
@ 1999-10-22 15:03 grant@intekom.com
  1999-10-22 15:36 ` Re: [SQL] Adding a rule to update a last_changed field Brook Milligan <brook@biology.nmsu.edu>
  1999-10-22 15:45 ` Re: [SQL] Adding a rule to update a last_changed field Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 7+ messages in thread

From: grant@intekom.com @ 1999-10-22 15:03 UTC (permalink / raw)
  To: pgsql-sql



I'm trying to use a rule to maintain a last_changed field. 

Using something like:

CREATE RULE rule1 AS
  ON UPDATE TO thetable
  DO UPDATE thetable SET last_changed = now()
  WHERE id = new.id;

This obviously doesn't work as the DO UPDATE triggers the rule
again, thus looping meaninglessly.

Is there a neat way to work around this? I'd like to just assign
now() to the last_changed field before the actual SQL update
code is run. 

Thanks





-----
Sent using MailStart.com ( http://MailStart.Com/welcome.html )
The FREE way to access your mailbox via any web browser, anywhere!




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Adding a rule to update a last_changed field
  1999-10-22 15:03 Adding a rule to update a last_changed field grant@intekom.com
@ 1999-10-22 15:36 ` Brook Milligan <brook@biology.nmsu.edu>
  1 sibling, 0 replies; 7+ messages in thread

From: Brook Milligan @ 1999-10-22 15:36 UTC (permalink / raw)
  To: grant@intekom.com; +Cc: pgsql-sql

   I'm trying to use a rule to maintain a last_changed field. 

   CREATE RULE rule1 AS
     ON UPDATE TO thetable
     DO UPDATE thetable SET last_changed = now()
     WHERE id = new.id;

Try instead something like

   CREATE RULE rule1 AS
     ON UPDATE TO thetable
     DO INSTEAD UPDATE thetable SET
	last_changed = now(),
	field1 = new.field1,
	field2 = new.field2,
	...
	fieldn = new.fieldn
     WHERE id = new.id;

That is, use an INSTEAD rule and replace each field explicitly.

Cheers,
Brook



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Adding a rule to update a last_changed field
  1999-10-22 15:03 Adding a rule to update a last_changed field grant@intekom.com
@ 1999-10-22 15:45 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 7+ messages in thread

From: Tom Lane @ 1999-10-22 15:45 UTC (permalink / raw)
  To: grant@intekom.com; +Cc: pgsql-sql

grant@intekom.com writes:
> I'm trying to use a rule to maintain a last_changed field. 

Use a trigger, not a rule.  You can adjust the last_changed
field before the tuple is stored that way.  There's an example
in the manual's discussion of triggers.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Adding a rule to update a last_changed field
@ 1999-10-22 16:05 Grant Kaufmann <grant@intekom.com>
  1999-10-22 16:41 ` Re: [SQL] Adding a rule to update a last_changed field Brook Milligan <brook@biology.nmsu.edu>
  0 siblings, 1 reply; 7+ messages in thread

From: Grant Kaufmann @ 1999-10-22 16:05 UTC (permalink / raw)
  To: pgsql-sql

This does not work, as it still calls a new UPDATE and re-invokes the rule.


> >   I'm trying to use a rule to maintain a last_changed field. 
> >
> >   CREATE RULE rule1 AS
> >     ON UPDATE TO thetable
> >     DO UPDATE thetable SET last_changed = now()
> >     WHERE id = new.id;
> 
> Try instead something like
> 
>    CREATE RULE rule1 AS
>      ON UPDATE TO thetable
>      DO INSTEAD UPDATE thetable SET
> 	last_changed = now(),
> 	field1 = new.field1,
> 	field2 = new.field2,
> 	...
> 	fieldn = new.fieldn
>      WHERE id = new.id;
> 
> That is, use an INSTEAD rule and replace each field explicitly.





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Adding a rule to update a last_changed field
  1999-10-22 16:05 Re: [SQL] Adding a rule to update a last_changed field Grant Kaufmann <grant@intekom.com>
@ 1999-10-22 16:41 ` Brook Milligan <brook@biology.nmsu.edu>
  0 siblings, 0 replies; 7+ messages in thread

From: Brook Milligan @ 1999-10-22 16:41 UTC (permalink / raw)
  To: grant@intekom.com; +Cc: pgsql-sql

Oops.

   This does not work, as it still calls a new UPDATE and re-invokes the rule.

You are right.  Your initial mention of using rules made me think of
all the cases I have used this technique for with views.  I forgot
that the two tables are the same.  What I meant to suggest (which may
not be relevant if you really have only one table) was create a rule
for a view of your table that will update the last_changed field in
the underlying table.  I have found that to be very useful in a bunch
of situations where I commonly have one table with lots of different
views and different ways of inserting/deleting/updating as appropriate
to each view.  Then the redirection via an INSTEAD rule and setting
last_* fields with rules is valuable.

Sorry about missing the fact that both of your tables were the same.

   >    CREATE RULE rule1 AS
   >      ON UPDATE TO thetable			<-- for views these are
   >      DO INSTEAD UPDATE thetable SET	<-- not the same table
   > 	last_changed = now(),
   > 	field1 = new.field1,
   > 	field2 = new.field2,
   > 	...
   > 	fieldn = new.fieldn
   >      WHERE id = new.id;

Cheers,
Brook



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Adding a rule to update a last_changed field
@ 1999-10-22 16:09 Grant Kaufmann <grant@intekom.com>
  1999-10-22 18:49 ` Re: [SQL] Adding a rule to update a last_changed field Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Grant Kaufmann @ 1999-10-22 16:09 UTC (permalink / raw)
  To: pgsql-sql


> > I'm trying to use a rule to maintain a last_changed field. 
> 
> Use a trigger, not a rule.  You can adjust the last_changed
> field before the tuple is stored that way.  There's an example
> in the manual's discussion of triggers.

My original choice was to use a trigger, but I don't want to introduce
C code when I don't have to. I don't believe that making external calls
should be necessary for a query that can be so simply expressed in SQL.

And from the manual:
  "The procedure name is the C function called"

If triggers can use SQL, I'd be happy to use them, but I can't find a single
example or description on how to do this.




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [SQL] Adding a rule to update a last_changed field
  1999-10-22 16:09 Re: [SQL] Adding a rule to update a last_changed field Grant Kaufmann <grant@intekom.com>
@ 1999-10-22 18:49 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 7+ messages in thread

From: Tom Lane @ 1999-10-22 18:49 UTC (permalink / raw)
  To: Grant Kaufmann <grant@intekom.com>; +Cc: pgsql-sql

Grant Kaufmann <grant@intekom.com> writes:
> My original choice was to use a trigger, but I don't want to introduce
> C code when I don't have to. I don't believe that making external calls
> should be necessary for a query that can be so simply expressed in SQL.

Who said anything about C code?  I was thinking of a pl/pgsql trigger,
actually.  The pl/pgsql section of the manual (programmer's guide under
procedural languages) has an example of almost exactly this problem.

If you do want to stick with rules then you'll need to make sure that
the underlying insert is to a different table than the one the insert
rule applies to --- in other words, create a view and have users insert
into the view.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~1999-10-22 18:49 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-10-22 15:03 Adding a rule to update a last_changed field grant@intekom.com
1999-10-22 15:36 ` Brook Milligan <brook@biology.nmsu.edu>
1999-10-22 15:45 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-10-22 16:05 Re: [SQL] Adding a rule to update a last_changed field Grant Kaufmann <grant@intekom.com>
1999-10-22 16:41 ` Brook Milligan <brook@biology.nmsu.edu>
1999-10-22 16:09 Re: [SQL] Adding a rule to update a last_changed field Grant Kaufmann <grant@intekom.com>
1999-10-22 18:49 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox