pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed[GENERAL] Creating rule for sliding data
4+ messages / 2 participants
[nested] [flat]
* [GENERAL] Creating rule for sliding data
@ 2011-10-09 10:22 F. BROUARD / SQLpro <sqlpro@club-internet.fr>
2011-10-09 16:50 ` Re: [GENERAL] Creating rule for sliding data F. BROUARD / SQLpro <sqlpro@club-internet.fr>
0 siblings, 1 reply; 4+ messages in thread
From: F. BROUARD / SQLpro @ 2011-10-09 10:22 UTC (permalink / raw)
To: pgsql-sql
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_DATE DATE 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 *************************
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [GENERAL] Creating rule for sliding data
2011-10-09 10:22 [GENERAL] Creating rule for sliding data F. BROUARD / SQLpro <sqlpro@club-internet.fr>
@ 2011-10-09 16:50 ` F. BROUARD / SQLpro <sqlpro@club-internet.fr>
2011-10-09 17:07 ` Re: [GENERAL] Creating rule for sliding data Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 4+ messages in thread
From: F. BROUARD / SQLpro @ 2011-10-09 16:50 UTC (permalink / raw)
To: pgsql-sql
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 *************************
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [GENERAL] Creating rule for sliding data
2011-10-09 10:22 [GENERAL] Creating rule for sliding data F. BROUARD / SQLpro <sqlpro@club-internet.fr>
2011-10-09 16:50 ` Re: [GENERAL] Creating rule for sliding data F. BROUARD / SQLpro <sqlpro@club-internet.fr>
@ 2011-10-09 17:07 ` Guillaume Lelarge <guillaume@lelarge.info>
2011-10-09 20:25 ` Re: [GENERAL] Creating rule for sliding data Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 4+ messages in thread
From: Guillaume Lelarge @ 2011-10-09 17:07 UTC (permalink / raw)
To: F. BROUARD / SQLpro <sqlpro@club-internet.fr>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [GENERAL] Creating rule for sliding data
2011-10-09 10:22 [GENERAL] Creating rule for sliding data F. BROUARD / SQLpro <sqlpro@club-internet.fr>
2011-10-09 16:50 ` Re: [GENERAL] Creating rule for sliding data F. BROUARD / SQLpro <sqlpro@club-internet.fr>
2011-10-09 17:07 ` Re: [GENERAL] Creating rule for sliding data Guillaume Lelarge <guillaume@lelarge.info>
@ 2011-10-09 20:25 ` Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 0 replies; 4+ messages in thread
From: Guillaume Lelarge @ 2011-10-09 20:25 UTC (permalink / raw)
To: F. BROUARD / SQLpro <sqlpro@club-internet.fr>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-10-09 20:25 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-10-09 10:22 [GENERAL] Creating rule for sliding data F. BROUARD / SQLpro <sqlpro@club-internet.fr>
2011-10-09 16:50 ` F. BROUARD / SQLpro <sqlpro@club-internet.fr>
2011-10-09 17:07 ` Guillaume Lelarge <guillaume@lelarge.info>
2011-10-09 20:25 ` Guillaume Lelarge <guillaume@lelarge.info>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox