DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:from:date:message-id:subject:to:content-type;
	b=GOhrTFK89yq7K+HNEadaNesat9gY+/uiaOfHPQiA4pYKmYJFnnNcRlB6v08VlQH5SK
	hqqw+LmpMmWG+hLCrvx57Deb0H479jDNzMc5LDCnkhvjLHCmwr633raYHBAecfHAHunS
	wcWsblO1UusVnskNWNUkkCWZkFuWD8qAWVcJE=
MIME-Version: 1.0
From: Amitabh Kant <amitabhkant@gmail.com>
Date: Thu, 27 Jan 2011 23:20:31 +0530
Message-ID: <AANLkTimLnBoEeBmFJHEWc1vQZuvPPsp920JWyQK8CABn@mail.gmail.com>
Subject: Automating PostgreSql table partition using triggers
To: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=0015174feb5e260583049ad7acb6

--0015174feb5e260583049ad7acb6
Content-Type: text/plain; charset=ISO-8859-1

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

--0015174feb5e260583049ad7acb6
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi<br><br>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.<br>

<br>Create Table tbltesting(<br>=A0testing_id int not null,<br>=A0testing_n=
ame character varying(255));<br><br>Create table tbltesting1(check(testing_=
id =3D 1)) inherits(tbltesting);<br>Create table tbltesting2(check(testing_=
id =3D 2)) inherits(tbltesting);<br>

<br>CREATE OR REPLACE FUNCTION partition_insert_trigger()<br>RETURNS TRIGGE=
R AS $$<br>DECLARE id integer ;<br>BEGIN<br>=A0=A0=A0 id :=3D NEW.testing_i=
d; <br>=A0=A0=A0 <br>=A0=A0=A0 INSERT INTO tbltesting&#39;||id||&#39; VALUE=
S (NEW.*);=A0=A0 //Problem line, not sure what syntax to use here<br>

<br>=A0=A0=A0 RETURN NULL;<br>END;<br>$$<br>LANGUAGE plpgsql;<br><br><br>CR=
EATE TRIGGER partition_trigger<br>=A0=A0=A0 BEFORE INSERT ON tbltesting<br>=
=A0=A0=A0 FOR EACH ROW EXECUTE PROCEDURE partition_insert_trigger();<br><br=
>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.<br>

<br><br>With regards<br><br>Amitabh<br><br>

--0015174feb5e260583049ad7acb6--