Non

sparky/ = 13 Mai, 2008 - 11:00

Sans l'héritage point de partition

[ Vous devez vous connecter pour poster des commentaires ]

mais, comment faire dans mon cas!

lola/ = 13 Mai, 2008 - 11:43

Bonjour,
et merci pour votre réponse, mais je ne vois pas comment faire dans mon cas, j'ai juste besoin de partitionner en utilisant une reference!
et je n'ai pas des champs communs à mettre dans la table mère et d'autres spécifiques à mettre dans chaque table fille!

ps: mon cas ressemble à l'exemple du poste precedent.

merci à celui qui veut bien m'aider à résoudre ce problème

librement

[ Vous devez vous connecter pour poster des commentaires ]

resolu

lola/ = 14 Mai, 2008 - 16:47

si ca peut aider quelqu'un d'autres:

--creation de la table mere:
CREATE TABLE sales_range
(salesman_id integer,
salesman_name text,
sales_amount integer,
sales_date timestamp);

------------------------------------------------------------
--creation des tables filles

CREATE TABLE sales_jan2008
( CHECK (sales_date < TO_DATE('02/01/2008','MM/DD/YYYY')) )
INHERITS (sales_range);

CREATE TABLE sales_fev2008
( CHECK (sales_date < TO_DATE('03/01/2008','MM/DD/YYYY')) )
INHERITS (sales_range);

CREATE TABLE sales_mar2008
( CHECK (sales_date < TO_DATE('04/01/2008','MM/DD/YYYY')) )
INHERITS (sales_range);

CREATE TABLE sales_avr2000
( CHECK (sales_date < TO_DATE('05/01/2008','MM/DD/YYYY')) )
INHERITS (sales_range);

CREATE TABLE sales_mai2008
( CHECK (sales_date < TO_DATE('06/01/2008','MM/DD/YYYY')) )
INHERITS (sales_range);

CREATE TABLE sales_juin2008
( CHECK (sales_date < TO_DATE('07/01/2008','MM/DD/YYYY')) )
INHERITS (sales_range);

CREATE TABLE sales_juil2008
( CHECK (sales_date < TO_DATE('08/01/2008','MM/DD/YYYY')) )
INHERITS (sales_range);

CREATE TABLE sales_aou2008
( CHECK (sales_date < TO_DATE('09/01/2008','MM/DD/YYYY')) )
INHERITS (sales_range);

CREATE TABLE sales_sep2008
( CHECK (sales_date < TO_DATE('10/01/2008','MM/DD/YYYY')) )
INHERITS (sales_range);

CREATE TABLE sales_oct2008
( CHECK (sales_date < TO_DATE('11/01/2008','MM/DD/YYYY')) )
INHERITS (sales_range);

CREATE TABLE sales_nov2008
( CHECK (sales_date < TO_DATE('12/01/2008','MM/DD/YYYY')) )
INHERITS (sales_range);

CREATE TABLE sales_dec2008
( CHECK (sales_date < TO_DATE('01/01/2009','MM/DD/YYYY')) )
INHERITS (sales_range);

-----------------------------------------------------------------
--creation d'une fonction pour insertions dans les tables filles

CREATE FUNCTION sales_range_handler()
RETURNS TRIGGER AS $$
DECLARE
BEGIN
IF tg_op = 'INSERT' THEN

IF new.sales_date < cast('2008-02-01' as timestamp)
THEN
INSERT INTO sales_jan2008
(salesman_id, salesman_name, sales_amount, sales_date)
VALUES (new.salesman_id, new.salesman_name,
new.sales_amount, new.sales_date);

ELSIF new.sales_date < cast('2008-03-01' as timestamp)
THEN
INSERT INTO sales_fev2008
(salesman_id, salesman_name, sales_amount, sales_date)
VALUES (new.salesman_id, new.salesman_name,
new.sales_amount, new.sales_date);

ELSIF new.sales_date < cast('2008-04-01' as timestamp)
THEN
INSERT INTO sales_mar2008
(salesman_id, salesman_name, sales_amount, sales_date)
VALUES (new.salesman_id, new.salesman_name,
new.sales_amount, new.sales_date);

ELSIF new.sales_date < cast('2008-05-01' as timestamp)
THEN
INSERT INTO sales_avr2008
(salesman_id, salesman_name, sales_amount, sales_date)
VALUES (new.salesman_id, new.salesman_name,
new.sales_amount, new.sales_date);

ELSIF new.sales_date < cast('2008-06-01' as timestamp)
THEN
INSERT INTO sales_mai2008
(salesman_id, salesman_name, sales_amount, sales_date)
VALUES (new.salesman_id, new.salesman_name,
new.sales_amount, new.sales_date);

ELSIF new.sales_date < cast('2008-07-01' as timestamp)
THEN
INSERT INTO sales_juin2008
(salesman_id, salesman_name, sales_amount, sales_date)
VALUES (new.salesman_id, new.salesman_name,
new.sales_amount, new.sales_date);

ELSIF new.sales_date < cast('2008-08-01' as timestamp)
THEN
INSERT INTO sales_juil2008
(salesman_id, salesman_name, sales_amount, sales_date)
VALUES (new.salesman_id, new.salesman_name,
new.sales_amount, new.sales_date);

ELSIF new.sales_date < cast('2008-09-01' as timestamp)
THEN
INSERT INTO sales_aou2008
(salesman_id, salesman_name, sales_amount, sales_date)
VALUES (new.salesman_id, new.salesman_name,
new.sales_amount, new.sales_date);

ELSIF new.sales_date < cast('2008-10-01' as timestamp)
THEN
INSERT INTO sales_sep2008
(salesman_id, salesman_name, sales_amount, sales_date)
VALUES (new.salesman_id, new.salesman_name,
new.sales_amount, new.sales_date);

ELSIF new.sales_date < cast('2008-11-01' as timestamp)
THEN
INSERT INTO sales_oct2008
(salesman_id, salesman_name, sales_amount, sales_date)
VALUES (new.salesman_id, new.salesman_name,
new.sales_amount, new.sales_date);

ELSIF new.sales_date < cast('2008-12-01' as timestamp)
THEN
INSERT INTO sales_nov2008
(salesman_id, salesman_name, sales_amount, sales_date)
VALUES (new.salesman_id, new.salesman_name,
new.sales_amount, new.sales_date);

ELSIF new.sales_date < cast('2009-01-01' as timestamp)
THEN
INSERT INTO sales_dec2008
(salesman_id, salesman_name, sales_amount, sales_date)
VALUES (new.salesman_id, new.salesman_name,
new.sales_amount, new.sales_date);

END IF;
ELSIF tg_op = 'UPDATE' THEN
-- Do the same for update
NULL;
ELSIF tg_op = 'DELETE' THEN
-- Do the same for delete
NULL;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-----------------------------------------------------------------
--creation d'un trigger pour déclencher la fonction à chaque insertion
CREATE TRIGGER sales_range_handler_trg
BEFORE INSERT OR UPDATE OR DELETE
ON sales_range
FOR EACH ROW
EXECUTE PROCEDURE sales_range_handler();

--insertions des enregistrements
--sales_range (salesman_id, salesman_name, sales_amount, sales_date)

INSERT INTO sales_range VALUES(1,'Lewis',1.25, cast('2008-01-15' as timestamp));
INSERT INTO sales_range VALUES(2,'Lewis',2.25, cast('2008-02-15' as timestamp));
INSERT INTO sales_range VALUES(3,'Lewis',3.25, cast('2008-03-15' as timestamp));
INSERT INTO sales_range VALUES(4,'Lewis',4.25, cast('2008-04-15' as timestamp));
INSERT INTO sales_range VALUES(5,'Lewis',5.25, cast('2008-05-15' as timestamp));
INSERT INTO sales_range VALUES(6,'Lewis',6.25, cast('2008-06-15' as timestamp));
INSERT INTO sales_range VALUES(7,'Lewis',7.25, cast('2008-07-15' as timestamp));
INSERT INTO sales_range VALUES(8,'Lewis',8.25, cast('2008-08-15' as timestamp));
INSERT INTO sales_range VALUES(9,'Lewis',9.25, cast('2008-09-15' as timestamp));
INSERT INTO sales_range VALUES(10,'Lewis',10.25, cast('2008-10-15' as timestamp));
INSERT INTO sales_range VALUES(11,'Lewis',11.25, cast('2008-11-15' as timestamp));
INSERT INTO sales_range VALUES(12,'Lewis',12.25, cast('2008-12-15' as timestamp));

partition=# select * from sales_range;
salesman_id | salesman_name | sales_amount | sales_date
-------------+---------------+--------------+---------------------
1 | Lewis | 1 | 2008-01-15 00:00:00
2 | Lewis | 2 | 2008-02-15 00:00:00
3 | Lewis | 3 | 2008-03-15 00:00:00
4 | Lewis | 4 | 2008-04-15 00:00:00
5 | Lewis | 5 | 2008-05-15 00:00:00
6 | Lewis | 6 | 2008-06-15 00:00:00
7 | Lewis | 7 | 2008-07-15 00:00:00
8 | Lewis | 8 | 2008-08-15 00:00:00
9 | Lewis | 9 | 2008-09-15 00:00:00
10 | Lewis | 10 | 2008-10-15 00:00:00
11 | Lewis | 11 | 2008-11-15 00:00:00
12 | Lewis | 12 | 2008-12-15 00:00:00
(12 rows)
-----------------------------------------------------------------
vérification des insertions dans les tables filles:

Librement

[ Vous devez vous connecter pour poster des commentaires ]

Ouverture de session

Navigation

Contactez-nous

Liens

Recherche

Sujets du forum

Sujets actifs

Nouveaux sujets:

Accéder aux archives

Syndication

Sondage

Partitionnement sous postgresql sans utiliser l'héritage

Technique - optimisation | Partitionnement sous postgresql sans utiliser l'héritage

Options d'affichage des commentaires

Non

mais, comment faire dans mon cas!

resolu