Lists: | pgsql-novice |
---|
From: | "Luc ROLLAND" <luc(at)rolland-fr(dot)com> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Create Rule |
Date: | 2004-02-17 20:01:51 |
Message-ID: | 000c01c3f590$e9809640$0100a8c0@lrp42600 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
Hello !
I would use a rule to store in each modified records the name of the user and the date of modification.
I try :
CREATE RULE shoe_mod AS ON UPDATE TO shoe_data
DO INSTEAD UPDATE shoe_data
SET shoename = NEW.shoename,
sh_avail = NEW.sh_avail,
slcolor = NEW.slcolor,
slminlen = NEW.slminlen,
slmaxlen = NEW.slmaxlen,
slunit = NEW.slunit,
shuser = current_user,
shdatmod = current_date
WHERE shoename = OLD.shoename ;
but I obtain an infinite loop ...
How can I do that.
Best regards.
Luc ROLLAND
From: | Jeremy Semeiks <jrs(at)denny(dot)farviolet(dot)com> |
---|---|
To: | Luc ROLLAND <luc(at)rolland-fr(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Create Rule |
Date: | 2004-02-17 21:14:15 |
Message-ID: | 20040217211415.GT31948@64.81.242.180 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
On Tue, Feb 17, 2004 at 09:01:51PM +0100, Luc ROLLAND wrote:
> Hello !
>
> I would use a rule to store in each modified records the name of the user and the date of modification.
> I try :
>
> CREATE RULE shoe_mod AS ON UPDATE TO shoe_data
> DO INSTEAD UPDATE shoe_data
> SET shoename = NEW.shoename,
> sh_avail = NEW.sh_avail,
> slcolor = NEW.slcolor,
> slminlen = NEW.slminlen,
> slmaxlen = NEW.slmaxlen,
> slunit = NEW.slunit,
> shuser = current_user,
> shdatmod = current_date
> WHERE shoename = OLD.shoename ;
>
> but I obtain an infinite loop ...
> How can I do that.
Hi Luc,
Your rule specifies to update the table, and updating the table
triggers the rule, which specifies to update the table... ad
infinitum.
Try creating the rule on a view instead, then updating the view:
CREATE RULE shoe_data_v AS SELECT * FROM shoe_data;
CREATE RULE shoe_v_mod AS ON UPDATE TO shoe_data_v
DO INSTEAD UPDATE shoe_data
...
HTH,
Jeremy
From: | joseph speigle <joe(dot)speigle(at)jklh(dot)us> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Create Rule |
Date: | 2004-02-21 10:26:13 |
Message-ID: | 20040221102613.GA26879@www.sirfsup.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
okay, its a novice list so I'll take a crack at this one.
I couldn't find create rule as ... select .... in the documentation, only rules for insert,update,delete.
Using it says ERROR: parser: parse error at or near "*" at character 43
(that;s the * for the select)
I think the problem comes from bastardizing the documentation. You should be doing this on a view, and then updating the real table. It should be "AS ON UPDATE TO shoe_data_view DO INSTEAD UPDATE real_shoe_data_table. The link in the doc is
http://www.sirfsup.com/sql_servers/postgresql/pg_docs/rules-update.html
half-way down the page.
On Tue, Feb 17, 2004 at 01:14:15PM -0800, Jeremy Semeiks wrote:
> On Tue, Feb 17, 2004 at 09:01:51PM +0100, Luc ROLLAND wrote:
> > Hello !
> >
> > I would use a rule to store in each modified records the name of the user and the date of modification.
> > I try :
> >
> > CREATE RULE shoe_mod AS ON UPDATE TO shoe_data
> > DO INSTEAD UPDATE shoe_data
> > SET shoename = NEW.shoename,
> > sh_avail = NEW.sh_avail,
> > slcolor = NEW.slcolor,
> > slminlen = NEW.slminlen,
> > slmaxlen = NEW.slmaxlen,
> > slunit = NEW.slunit,
> > shuser = current_user,
> > shdatmod = current_date
> > WHERE shoename = OLD.shoename ;
> >
> > but I obtain an infinite loop ...
> > How can I do that.
>
> Hi Luc,
>
> Your rule specifies to update the table, and updating the table
> triggers the rule, which specifies to update the table... ad
> infinitum.
>
> Try creating the rule on a view instead, then updating the view:
>
> CREATE RULE shoe_data_v AS SELECT * FROM shoe_data;
> CREATE RULE shoe_v_mod AS ON UPDATE TO shoe_data_v
> DO INSTEAD UPDATE shoe_data
> ...
>
> HTH,
> Jeremy
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
--
joe speigle
www.sirfsup.com