Re: [GENERAL] Creating rule for sliding data

On Sun, 2011-10-09 at 21:17 +0200, F. BROUARD / SQLpro wrote:
> Hi,
> 
> Le 09/10/2011 19:07, Guillaume Lelarge a écrit :
> > Hi,
> >
> > On Sun, 2011-10-09 at 18:50 +0200, F. BROUARD / SQLpro wrote:
> >> I am answering to myseilf...
> >>
> >> the good syntax is something like :
> >>
> >>
> >> CREATE RULE R_U_MSR_BEFORE2000x
> >> AS
> >> ON UPDATE TO T_MESURE_MSR
> >>      WHERE ( NEW.MSR_DATE<  DATE '2000-01-01' )
> >> DO INSTEAD
> >> (
> >> -- rows does not change partition :
> >>     UPDATE T_MESURE_BEFORE2000_MSR
> >>     SET MSR_ID     = NEW.MSR_ID,
> >>         MSR_DATE   = NEW.MSR_DATE,
> >>         MSR_MESURE = NEW.MSR_MESURE
> >>     WHERE ( OLD.MSR_DATE<  DATE '2000-01-01' );
> >> -- rows does change partition (first INSERT NEWs then DELETE OLDs)
> >>     INSERT INTO T_MESURE_MSR
> >>     SELECT MSR_ID,
> >>            MSR_DATE,
> >>            MSR_MESURE
> >>     FROM   NEW
> >>     WHERE NOT ( OLD.MSR_DATE<  DATE '2000-01-01' ); ;
> >>     DELETE FROM T_MESURE_MSR
> >>     WHERE  MSR_ID = OLD.MSR_ID
> >>       AND  MSR_DATE = OLD.MSR_DATE
> >>       AND  MSR_MESURE = OLD.MSR_MESURE
> >>       AND  NOT ( OLD.MSR_DATE<  DATE '2000-01-01' );
> >> );
> >>
> >> The problem is nowhere in the doc there is a mention where much more
> >> than one commande must be place into brackets !
> >>
> >
> > As a matter of fact, it does:
> >
> > CREATE [ OR REPLACE ] RULE name AS ON event
> >   TO table [ WHERE condition ]
> >   DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }
> >
> >                                               ^                       ^
> >                                               |                       |
> > See -----------------------------------------+------------------------
> >
> > Extract from
> > http://www.postgresql.org/docs/9.1/interactive/sql-createrule.html
> >
> >
> 
> Please give a real example instead of copying the doc that I have read a 
> lot !
> 
> I am not so stupid to have post this topic without having try many 
> syntaxes wich does not works !
> 

I don't think you're stupid. You said the doc was wrong, and I answered
you it wasn't. But I understand it didn't help you solve your issue...

Anyway, if you gave us the error message, it would be easier to answer
you. Here is the error message I get:

ERROR:  relation "new" does not exist
LINE 18:    FROM   NEW                  ^

And actually, you can't use "FROM NEW". And this:
 INSERT INTO T_MESURE_MSR SELECT MSR_ID, MSR_DATE, MSR_MESURE FROM   NEW WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01'
)

has no meaning at all in PostgreSQL.


-- 
Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com