pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAdding 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
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:36 Brook Milligan <brook@biology.nmsu.edu>
parent: grant@intekom.com
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:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: grant@intekom.com
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>
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:09 Grant Kaufmann <grant@intekom.com>
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:41 Brook Milligan <brook@biology.nmsu.edu>
parent: Grant Kaufmann <grant@intekom.com>
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 18:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Grant Kaufmann <grant@intekom.com>
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