Hi
I'm attempting to do some partitioning in a database and am wondering
if I can use the data being inserted to insert into new schema.
I have the following in the public schema:
create table test (id serial, note varchar not null, schema varchar not null)
then create a schema:
create schema "1-2-3";
create schema "4-5-6";
and 2 tables:
create table "1-2-3".test () inherits public.test;
create table "1-2-3".test () inherits public.test;
I then want something similar to:
create or replace function test() returns trigger as $$ begin insert
into NEW.schema.test values (NEW.*); return null; end; $$ language
plpgsql;
create trigger test_insert before insert on test for each row execute
procedure test();
so that:
insert into test (schema, note) values ('1-2-3', 'some note data');
would result in the data going into the table "1-2-3".test
This doesn't work as expected as the "NEW.schema.test" isn't
substituted with "1-2-3". I don't think I can use an if statement as I
will have an unknown number of schemas.
Thanks
Jake