From: | "Josh Harrison" <joshques(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | update problem in partitioned tables |
Date: | 2007-09-12 15:35:10 |
Message-ID: | 8d89ea1d0709120835o50d2020y514a812d40891f55@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mike Charnoky | 2007-09-12 15:42:20 | problems with large table |
Previous Message | Dennis Muhlestein | 2007-09-12 14:51:19 | Re: Timestamp from an OID? |