Re: 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(at)gmail(dot)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

On 9/12/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Josh Harrison" <joshques(at)gmail(dot)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
>