Re: Inherit from tables to one main table

>I have managed to create the main table for all 9 tables to go into but it
does not seem to show any information. How do I get the data from the 9
tables into this main table. Am I missing something that I forgot to add to
it or do I need a trigger of some sort in it. At a later date this table has
to pick up any changes from any of the nine tables or all of the tables and
be updated as it goes along. Would the trigger be a useful thing to use so
that it will fix these issues. I have never used triggers so I'm not a 100%
sure how to go about doing that.

Look at process of partitioning explained with the help of example:
1. Create master table
create table master(org int, name varchar(10));
2. Create child tables/partitions
create table master_part1 (CHECK (org < 6) ) inherits (master);
create table master_part2 (CHECK (org >=6 and org <=10 ) )
inherits(master);
3. Now you to define * Rule OR tiggrer * for * insert,delete,update*
operations:
Way-1: Using Rule for insert operation:
CREATE OR REPLACE RULE insert_master_p1
AS ON INSERT TO master
WHERE (org <6)
DO INSTEAD
insert into master_part1 values(NEW.org, NEW.name);

CREATE OR REPLACE RULE insert_master_p2
AS ON INSERT TO master
WHERE (org >=6 and org <=10 )
DO INSTEAD
insert into master_part2 values (New.org,New.name);

Way-2: Using Trigger for insert operation:
CREATE OR REPLACE FUNCTION master_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.ORG < 6) THEN
INSERT INTO master_part1 VALUES (NEW.*);
ELSIF ( NEW.ORG >= 6 AND NEW.ORG <11) THEN
INSERT INTO master_part2 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Organization out of range. Fix the
master_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_master
BEFORE INSERT ON master
FOR EACH ROW EXECUTE PROCEDURE master_insert_trigger ();

You can apply same logic to setup your use case.

-----
Greetings,
Samrat Revagade,
NTT DATA OSS Center Pune, India.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Inherit-from-tables-to-one-main-table-tp5769870p5769876.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.