Postgres Pro
Downloads
Home   >   mailing lists

Thread: Create Rule

Create Rule

From
"Luc ROLLAND"
Date:
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

Re: Create Rule

From
"Luc ROLLAND"
Date:
Hi jeremy !
 
Your solution (creating the rule on a view, then updating the view) works fine !
 
Thanks.

Re: Create Rule

From
Jeremy Semeiks
Date:
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

Re: Create Rule

From
joseph speigle
Date:
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
thereal table.  It should be "AS ON UPDATE TO shoe_data_view DO INSTEAD UPDATE real_shoe_data_table.  The link in the
docis  
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@postgresql.org so that your
>       message can get through to the mailing list cleanly

--
joe speigle
www.sirfsup.com