Re: Automating PostgreSql table partition using triggers

<div class="gmail_quote">On Thu, Jan 27, 2011 at 11:20 PM, Amitabh Kant <span dir="ltr"><<a
href="mailto:amitabhkant@gmail.com">amitabhkant@gmail.com</a>></span>wrote:<br /><blockquote class="gmail_quote"
style="margin:0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"> Hi<br /><br />I am
tryingto write a function which is being called from a trigger used for partitioning a large table. The partitioning is
tohappen based on an integer field (testing_id). A simplified structure of what I am trying to do is written below.<br
/><br/>Create Table tbltesting(<br /> testing_id int not null,<br /> testing_name character varying(255));<br /><br
/>Createtable tbltesting1(check(testing_id = 1)) inherits(tbltesting);<br />Create table tbltesting2(check(testing_id =
2))inherits(tbltesting);<br /><br />CREATE OR REPLACE FUNCTION partition_insert_trigger()<br />RETURNS TRIGGER AS $$<br
/>DECLAREid integer ;<br />BEGIN<br />    id := NEW.testing_id; <br />    <br />    INSERT INTO tbltesting'||id||'
VALUES(NEW.*);   //Problem line, not sure what syntax to use here<br /><br />    RETURN NULL;<br />END;<br />$$<br
/>LANGUAGEplpgsql;<br /><br /><br />CREATE TRIGGER partition_trigger<br />    BEFORE INSERT ON tbltesting<br />    FOR
EACHROW EXECUTE PROCEDURE partition_insert_trigger();<br /><br />Creating tables or child tables is not a problem and
thetrigger works fine if the function has static definitions. What I am trying to achieve is use the new testing_id to
createa table name for use in the insert statement. If I am able to use the variable in the table name, I would not
haveto re-declare the function each time with modified conditions for each separate testing_id.<br /><br /><br />With
regards<br/><font color="#888888"><br />Amitabh<br /><br /></font></blockquote></div><br />Forgot to add that I am
using8.4.<br /><br />With regards<br /><br />Amitabh<br />