Skip site navigation (1) Skip section navigation (2)

Are trigger functions able to generate table name dynamically?

From: Sergey Samokhin <prikrutil(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Are trigger functions able to generate table name dynamically?
Date: 2009-09-26 13:51:54
Message-ID: e42595410909260651h16f09d9bkd4972de036fd685e@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hello!

"5.9.2. Implementing Partitioning" chapter of the official
documentation shows how to redirect data inserted into the master
table to the appropriate partition one by using a trigger function. I
like the idea of redirecting data in such a way, but having to
hardcode rules on which row to be inserted into which table makes me
cry:

IF ( NEW.logdate >= DATE '2006-02-01' AND
         NEW.logdate < DATE '2006-03-01' ) THEN
        INSERT INTO measurement_y2006m02 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
            NEW.logdate < DATE '2006-04-01' ) THEN
        INSERT INTO measurement_y2006m03 VALUES (NEW.*);
...

When I first read that chapter I decided that it's what I should use
in order to give users a nice interface to my partitions.
Unfortunatelly I can't hardcode rules, because a) there is too much
partitions to hardcode b) they are created dynamically

Is it possible for a trigger function to redirect a row into a
partiton table denoted by a certain column of the row?

For example, I want data in the following query to be inserted into
the table "some_value_suffix" (this table name is supposed be computed
dynamically in the trigger funcions by concatenating the 'some_value'
and suffix '_suffix'):

INSERT INTO master_table VALUES ('some_value', 42);

Can this behaviour be achieved with trigger functions?

I just don't know what is possible with them, because I haven't read
all the related docs.

Thanks.

-- 
Sergey Samokhin

pgsql-novice by date

Next:From: Sergey SamokhinDate: 2009-09-26 15:52:00
Subject: GRANT SELECT on all the partitions?
Previous:From: waltyDate: 2009-09-26 08:27:04
Subject: text field slow to display in pgadmin

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group