Re: Using data for column names in plpgsql

On Fri, Mar 25, 2011 at 6:18 PM, Jake Stride <jake(at)stride(dot)me(dot)uk> 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