Re: Automating PostgreSql table partition using triggers
От | Viktor Bojović |
---|---|
Тема | Re: Automating PostgreSql table partition using triggers |
Дата | |
Msg-id | AANLkTin_xvPOg16n4gEhaD2trEsE3GyD0XnTc4KsxdEc@mail.gmail.com обсуждение исходный текст |
Ответ на | Automating PostgreSql table partition using triggers (Amitabh Kant <amitabhkant@gmail.com>) |
Ответы |
Re: Automating PostgreSql table partition using triggers
(Amitabh Kant <amitabhkant@gmail.com>)
|
Список | pgsql-sql |
when creating dynamic query try to store it completey as string because you will not be able to change tableName (i haventbeen doing that for a long time , so possibly i can be wrong).<br />to "exec" or "execute" command you have to passthe query as parameter.<br /> it would look like this<br /><br />declare sql as varchar;<br />Field2=new. testing_name;<br/>Field1=new.testing_id;<br />sql:='insert into tableName'||id||' (testing_id,testing_name) values ('||Field1||','||quote_literal(Field2)||')';<br/> exec(sql);<br /><br /><div class="gmail_quote">On Thu, Jan 27, 2011 at6:50 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 trying to write a function which is being called from a trigger used for partitioninga large table. The partitioning is to happen based on an integer field (testing_id). A simplified structure ofwhat I am trying to do is written below.<br /><br />Create Table tbltesting(<br /> testing_id int not null,<br /> testing_namecharacter varying(255));<br /><br />Create table tbltesting1(check(testing_id = 1)) inherits(tbltesting);<br/>Create table tbltesting2(check(testing_id = 2)) inherits(tbltesting);<br /><br />CREATE OR REPLACEFUNCTION partition_insert_trigger()<br />RETURNS TRIGGER AS $$<br />DECLARE id integer ;<br />BEGIN<br /> id :=NEW.testing_id; <br /> <br /> INSERT INTO tbltesting'||id||' VALUES (NEW.*); //Problem line, not sure what syntaxto use here<br /><br /> RETURN NULL;<br />END;<br />$$<br />LANGUAGE plpgsql;<br /><br /><br />CREATE TRIGGER partition_trigger<br/> BEFORE INSERT ON tbltesting<br /> FOR EACH ROW EXECUTE PROCEDURE partition_insert_trigger();<br/><br />Creating tables or child tables is not a problem and the trigger works fine if thefunction has static definitions. What I am trying to achieve is use the new testing_id to create a table name for usein the insert statement. If I am able to use the variable in the table name, I would not have to re-declare the functioneach 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 /><br clear="all" /><br />-- <br />---------------------------------------<br/>Viktor Bojović<br />---------------------------------------<br />Wherever Igo, Murphy goes with me<br /><div id="avg_ls_inline_popup" style="visibility: hidden; left: -5000px; position: absolute;z-index: 9999; padding: 0px; margin-left: 0px; margin-top: 0px; overflow: hidden; word-wrap: break-word; color:black; font-size: 10px; text-align: left; line-height: 130%;"></div>