[SQL] [GENERAL] Creating rule for sliding data

2011-10-09 Thread F. BROUARD / SQLpro 

Hello,

I have a problem to find the good syntax for a rule for rows going for 
one partition to the other in cas of an update.


Let me give the conditions :

1 - having a mother table

CREATE TABLE T_MESURE_MSR
(
  MSR_ID  INT   NOT NULL,
  MSR_DATEDATE  NOT NULL,
  MSR_MESURE  FLOAT NOT NULL
);

2 - having 2 child table :

CREATE TABLE T_MESURE_BEFORE2000_MSR
(
CHECK ( MSR_DATE < DATE '2000-01-01')
) INHERITS (T_MESURE_MSR)

CREATE TABLE T_MESURE_AFTER1999_MSR
(
CHECK ( MSR_DATE >= DATE '2000-01-01')
) INHERITS (T_MESURE_MSR)

THE QUESTION...

How to make the proper rule for T_MESURE_MSR il a rox goes from 1998 to 
2003 ?


This one does not work :

CREATE RULE R_U_MSR_BEFORE2000
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
  VALUES ( NEW.MSR_ID,
   NEW.MSR_DATE,
   NEW.MSR_MESURE )
  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
  WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' );


And no more for this one :

CREATE RULE R_U_MSR_BEFORE2000
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 the NEWs then DELETE the 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, MSR_DATE, MSR_MESURE)
 IN (SELECT MSR_ID, MSR_DATE, MSR_MESURE
 FROM   OLD
 WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' ));

Any idea ?

Thanks


--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR  :  http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*** http://www.sqlspot.com *


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] [GENERAL] Creating rule for sliding data

2011-10-09 Thread F. BROUARD / SQLpro 

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 !


A +

--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR  :  http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*** http://www.sqlspot.com *


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] [GENERAL] Creating rule for sliding data

2011-10-09 Thread Guillaume Lelarge 
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


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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] [GENERAL] Creating rule for sliding data

2011-10-09 Thread Guillaume Lelarge 
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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql