On Fri, Mar 25, 2011 at 6:18 PM, Jake Stride <jake@xxxxxxxxxxxx> wrote: > 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. You can do it with 'execute' with some record to text (and back) kung fu, or an hstore, but this is not a good approach in a high performance trigger. Another method is to proxy the insert through a pure sql (not plpgsql) function and mess with the search_path before calling it. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general