Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 09D831337B7A for ; Thu, 27 Jan 2011 13:52:33 -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 56065-01 for ; Thu, 27 Jan 2011 17:52:25 +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 9A49F1337B79 for ; Thu, 27 Jan 2011 13:52:24 -0400 (AST) Received: by eye27 with SMTP id 27so1105583eye.19 for ; Thu, 27 Jan 2011 09:52:23 -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:content-type; bh=MImQjw5a8BjPvPN3fCGbkspUECxe8bDn3MFYixDzRlw=; b=lg4IhFcfQOflYxrFf7jNwZ37VWhT/pmwCCvdCLVMPuOD3qUMFUVqlFW/zkG5TWJppV Q9avZhdNqC3hOSAxUN75I8yyCf3JiSy1YUJmQ5rjjEd2BzYb9DmDgjSUX0co5YDWW6il AvkAq7069lWeZiIAweQCJQRRiDnU6XnDZQ0Mw= 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 :content-type; b=s9+z5nvsvUXiKBXHs9mFrV2U9+jbt+3NJWbwvIUMYYbDI/RoSer1WEObLlNajI66Tc pYcFOBV2WOSMGOmbXKXf7kWFd8jietWDxUp7YQhiG22xjXq7JcePLuCmK+DpQ0+jDMaZ Sroxo0De+LseF9MF4InKxPoTbL5TVisiOMt4Q= Received: by 10.213.8.210 with SMTP id i18mr3447907ebi.3.1296150743309; Thu, 27 Jan 2011 09:52:23 -0800 (PST) MIME-Version: 1.0 Received: by 10.213.14.82 with HTTP; Thu, 27 Jan 2011 09:52:03 -0800 (PST) In-Reply-To: References: From: Amitabh Kant Date: Thu, 27 Jan 2011 23:22:03 +0530 Message-ID: Subject: Re: Automating PostgreSql table partition using triggers To: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0015174feb32a161f5049ad79c04 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.588 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201101/107 X-Sequence-Number: 35054 --0015174feb32a161f5049ad79c04 Content-Type: text/plain; charset=ISO-8859-1 On Thu, Jan 27, 2011 at 11:20 PM, Amitabh Kant wrote: > 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 > > Forgot to add that I am using 8.4. With regards Amitabh --0015174feb32a161f5049ad79c04 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
On Thu, Jan 27, 2011 at 11:20 PM, Amitabh Kant <= span dir=3D"ltr"><amitabhkant@g= mail.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(
=A0testing_id int not null,
=A0testing_n= ame 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 TRIGGE= R AS $$
DECLARE id integer ;
BEGIN
=A0=A0=A0 id :=3D NEW.testing_i= d;
=A0=A0=A0
=A0=A0=A0 INSERT INTO tbltesting'||id||' VALUE= S (NEW.*);=A0=A0 //Problem line, not sure what syntax to use here

=A0=A0=A0 RETURN NULL;
END;
$$
LANGUAGE plpgsql;


CR= EATE TRIGGER partition_trigger
=A0=A0=A0 BEFORE INSERT ON tbltesting
= =A0=A0=A0 FOR EACH ROW EXECUTE PROCEDURE partition_insert_trigger();
Creating tables or child tables is not a problem and the trigger works fin= e if the function has static definitions. What I am trying to achieve is us= e 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 r= e-declare the function each time with modified conditions for each separate= testing_id.


With regards

Amitabh


Forgot to add that I am using 8.4.

Wit= h regards

Amitabh
--0015174feb32a161f5049ad79c04--