Using data for column names in plpgsql

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