Using data for column names in plpgsql - Mailing list pgsql-general

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