Re: [GENERAL] Creating rule for sliding data

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 eventTO table [ WHERE condition ]DO [ ALSO | INSTEAD ] { NOTHING | command | (
command; command ... ) }
 
                                            ^                       ^                                            |
                |
 
See -----------------------------------------+------------------------

Extract from
http://www.postgresql.org/docs/9.1/interactive/sql-createrule.html


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