From: | Amitabh Kant <amitabhkant(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Automating PostgreSql table partition using triggers |
Date: | 2011-01-27 17:50:31 |
Message-ID: | AANLkTimLnBoEeBmFJHEWc1vQZuvPPsp920JWyQK8CABn@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi
I am trying to write a function which is being called from a trigger used
for partitioning a large table. The partitioning is to happen based on an
integer field (testing_id). A simplified structure of what I am trying to do
is written below.
Create Table tbltesting(
testing_id int not null,
testing_name character varying(255));
Create table tbltesting1(check(testing_id = 1)) inherits(tbltesting);
Create table tbltesting2(check(testing_id = 2)) inherits(tbltesting);
CREATE OR REPLACE FUNCTION partition_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE id integer ;
BEGIN
id := NEW.testing_id;
INSERT INTO tbltesting'||id||' VALUES (NEW.*); //Problem line, not
sure what syntax to use here
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER partition_trigger
BEFORE INSERT ON tbltesting
FOR EACH ROW EXECUTE PROCEDURE partition_insert_trigger();
Creating tables or child tables is not a problem and the trigger works fine
if the function has static definitions. What I am trying to achieve is use
the new testing_id to create a table name for use in the insert statement.
If I am able to use the variable in the table name, I would not have to
re-declare the function each time with modified conditions for each separate
testing_id.
With regards
Amitabh
From | Date | Subject | |
---|---|---|---|
Next Message | Amitabh Kant | 2011-01-27 17:52:03 | Re: Automating PostgreSql table partition using triggers |
Previous Message | Thomas Kellerer | 2011-01-27 15:30:02 | Re: how to get row number in select query |