Thread: update problem in partitioned tables

Hi
I have the master-child tables as follows

Master table
create table foo(a numeric(10,0));

first child table foo1 as
create table foo1 (check(a>=0 and a<10)) inherits(foo)

second child table foo2 as
create table foo1 (check(a>=10) inherits(foo)

Now I create this function to redirect the inserts  and updates on the master table.

CREATE OR REPLACE FUNCTION foo_function () RETURNS trigger AS
'
BEGIN
 IF (TG_OP = ''INSERT'') THEN        
   IF NEW.a >= 0  and NEW.a <10 THEN
   INSERT INTO foo1 (a) values(new.a);
 
    ELSIF (NEW.a >=10  ) THEN
   INSERT INTO foo2 (a) values( new.a);
  end if;

ELSIF (TG_OP = ''UPDATE'') THEN
    IF (OLD.a>=0 AND OLD.a < 10 AND NEW.a >= 10) THEN

      INSERT INTO foo2 (a)  VALUES (NEW.a);
      DELETE FROM foo1  WHERE foo1.a = OLD.a;
       
      END IF;
END IF;
RETURN NULL;
END
' LANGUAGE 'plpgsql'
GO

And I create a before insert/update trigger on foo

CREATE TRIGGER FOO_TRIGGER
 BEFORE INSERT OR UPDATE
  ON FOO
  FOR EACH ROW
  EXECUTE PROCEDURE foo_function();

The insert statements are working properly.ie., they are inserting the rows into the corresponding child tables.
But the update statements involving migration of row from 1 child to another is not working

for example
UPDATE FOO SET A=5 WHERE A=4 works fine becoz the updated row remains in the same child

But
UPDATE FOO SET A =20 WHERE A=4 gives the following error
"new row for relation "foo1" violates check constraint "foo1_a_check"

Why? All I wanted to do in this case is to  insert that row into 'foo2' table and delete it from 'foo1' table . What am I doing wrong here?
(Note: If I implement the same using rules it works fine!!!)

Thanks in advance
josh

"Josh Harrison" <joshques@gmail.com> writes:
> Now I create this function to redirect the inserts  and updates on the
> master table.

Uh ... there never will be any updates in the master table, since it
hasn't got any entries.

What you'd need is an insert trigger on the master and update triggers
on each child table.  I think the logic of this trigger is wrong for
the update case anyway.

            regards, tom lane

Thanks. Check this website that talk abt the approach that I had mentioned
http://blogs.ittoolbox.com//oracle/guide/archives/comparing-partitioned-tables-in-oracle-and-enterprisedbpostgresql-13261

I also tried your method (triggers on the child table) and it gives the same error too.

Josh