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.