I was just messing around with postgresql and table partitions and tablespaces. Here is a short SQL script that uses a virtual table that is partitioned by the modulus of the id column. Each partition lies on a different tablespace and in the schema partition.
begin;
drop table if exists partition.test_0;
drop table if exists partition.test_1;
drop table if exists test;
CREATE TABLE test (
id SERIAL PRIMARY KEY
);
CREATE TABLE partition.test_0 (
PRIMARY KEY (id),
CHECK ( id % 2 = 0 )
)
INHERITS (test)
TABLESPACE ts1;
CREATE TABLE partition.test_1 (
PRIMARY KEY (id),
CHECK ( id % 2 = 1 )
)
INHERITS (test)
TABLESPACE ts2;
CREATE OR REPLACE FUNCTION test_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.id % 2 = 0 ) THEN
INSERT INTO partition.test_0 VALUES (NEW.*);
ELSIF ( NEW.id % 2 = 1 ) THEN
INSERT INTO partition.test_1 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'This should not happen! See test_insert_trigger.';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER insert_test_mod_trigger
BEFORE INSERT ON test
FOR EACH ROW EXECUTE PROCEDURE test_trigger_function();
commit;