Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 507611337B4F for ; Thu, 27 Jan 2011 22:41:38 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 02756-06 for ; Fri, 28 Jan 2011 02:41:32 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ey0-f174.google.com (mail-ey0-f174.google.com [209.85.215.174]) by mail.postgresql.org (Postfix) with ESMTP id BD8B31337B4E for ; Thu, 27 Jan 2011 22:41:29 -0400 (AST) Received: by eye27 with SMTP id 27so1356953eye.19 for ; Thu, 27 Jan 2011 18:41:29 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc:content-type; bh=+1o8rGAb90wBkTyHT2RRoco8NKyMRkIni99QTT6Zj3o=; b=XjnGcpnXqxjEQbOQsLlN3VSIaKdG4gr574YdEKj/ctCvxVzBq0KJYtUIX5r224Nfzl BMZri1UgN9jDwaHC6Bu1XhZRtnky9JPgUE9fAmJJjUhGQxvrxff3u53pw9/WoMuHTzDe B11+RvwO5SWFgXim7YCzmfWL4BP88GLKGE5S4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; b=nx7VibqK2C7mhZgS6Lt84EEB2SsJhvOByrdjT3CJEYoXXlVv1oMkZvfLKRJVsNKhDT C531ojuWqIsvZooFvdfxlqblJrhMIzhQPwJJaRSWk0LjGGOL/UM60a/eSNzafgfOFvxi PzVJ7oRUSWoKPC1aLoUzMdUgshXxt87wUnVdY= Received: by 10.213.26.15 with SMTP id b15mr4117574ebc.16.1296182489387; Thu, 27 Jan 2011 18:41:29 -0800 (PST) MIME-Version: 1.0 Received: by 10.213.14.82 with HTTP; Thu, 27 Jan 2011 18:41:09 -0800 (PST) In-Reply-To: References: From: Amitabh Kant Date: Fri, 28 Jan 2011 08:11:09 +0530 Message-ID: Subject: Re: Automating PostgreSql table partition using triggers To: =?UTF-8?Q?Viktor_Bojovi=C4=87?= Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0015174a050ad8132e049adf00a4 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7 X-Spam-Level: X-Archive-Number: 201101/111 X-Sequence-Number: 35058 --0015174a050ad8132e049adf00a4 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Fri, Jan 28, 2011 at 2:40 AM, Viktor Bojovi=C4=87 wrote: > when creating dynamic query try to store it completey as string because y= ou > will not be able to change tableName (i havent been doing that for a long > time , so possibly i can be wrong). > to "exec" or "execute" command you have to pass the query as parameter. > it would look like this > > declare sql as varchar; > Field2=3Dnew. testing_name; > Field1=3Dnew.testing_id; > sql:=3D'insert into tableName'||id||' (testing_id,testing_name) values > ('||Field1||','||quote_literal(Field2)||')'; > exec(sql); > > On Thu, Jan 27, 2011 at 6:50 PM, Amitabh Kant wrot= e: > >> Hi >> >> I am trying to write a function which is being called from a trigger use= d >> for partitioning a large table. The partitioning is to happen based on a= n >> integer field (testing_id). A simplified structure of what I am trying t= o do >> is written below. >> >> Create Table tbltesting( >> testing_id int not null, >> testing_name character varying(255)); >> >> Create table tbltesting1(check(testing_id =3D 1)) inherits(tbltesting); >> Create table tbltesting2(check(testing_id =3D 2)) inherits(tbltesting); >> >> CREATE OR REPLACE FUNCTION partition_insert_trigger() >> RETURNS TRIGGER AS $$ >> DECLARE id integer ; >> BEGIN >> id :=3D 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 n= ot >> have to re-declare the function each time with modified conditions for e= ach >> separate testing_id. >> >> >> With regards >> >> Amitabh >> >> -- > --------------------------------------- > Viktor Bojovi=C4=87 > --------------------------------------- > Wherever I go, Murphy goes with me > Thanks Victor. This should work out for me. Amitabh --0015174a050ad8132e049adf00a4 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
On Fri, Jan 28, 2011 at 2:40 AM, Viktor Bojovi= =C4=87 <vi= ktor.bojovic@gmail.com> wrote:
when creating dynamic query try to store it completey as string because you= will not be able to change tableName (i havent been doing that for a long = time , so possibly i can be wrong).
to "exec" or "execute= " command you have to pass the query as parameter.
it would look like this

declare sql as varchar;
Field2=3Dnew. tes= ting_name;
Field1=3Dnew.testing_id;
sql:=3D'insert into tableName= '||id||' (testing_id,testing_name) values ('||Field1||',= 9;||quote_literal(Field2)||')';
exec(sql);

On Thu, Jan = 27, 2011 at 6:50 PM, Amitabh Kant <amitabhkant@gmail.com> wrote:
Hi

I am trying to write a function which is being called from a trig= ger used for partitioning a large table. The partitioning is to happen base= d on an integer field (testing_id). A simplified structure of what I am try= ing to do is written below.

Create Table tbltesting(
=C2=A0testing_id int not null,
=C2=A0tes= ting_name character varying(255));

Create table tbltesting1(check(te= sting_id =3D 1)) inherits(tbltesting);
Create table tbltesting2(check(te= sting_id =3D 2)) inherits(tbltesting);

CREATE OR REPLACE FUNCTION partition_insert_trigger()
RETURNS TRIGGE= R AS $$
DECLARE id integer ;
BEGIN
=C2=A0=C2=A0=C2=A0 id :=3D NEW.= testing_id;
=C2=A0=C2=A0=C2=A0
=C2=A0=C2=A0=C2=A0 INSERT INTO tblte= sting'||id||' VALUES (NEW.*);=C2=A0=C2=A0 //Problem line, not sure = what syntax to use here

=C2=A0=C2=A0=C2=A0 RETURN NULL;
END;
$$
LANGUAGE plpgsql;
<= br>
CREATE TRIGGER partition_trigger
=C2=A0=C2=A0=C2=A0 BEFORE INSERT= ON tbltesting
=C2=A0=C2=A0=C2=A0 FOR EACH ROW EXECUTE PROCEDURE partiti= on_insert_trigger();

Creating tables or child tables is not a proble= m 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 c= onditions for each separate testing_id.


With regards

Amitabh

--
-------= --------------------------------
Viktor Bojovi=C4=87
----------------= -----------------------
Wherever I go, Murphy goes with me

Thanks Victor. This should work out for = me.


Amitabh
--0015174a050ad8132e049adf00a4--