From: Amitabh Kant <amitabhkant@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Automating PostgreSql table partition using triggers
Date: Thu, 27 Jan 2011 23:22:03 +0530
Message-ID: <AANLkTimuyMX0WDdYg67CLzpSe0DV00jVkM90g5P0ABCG@mail.gmail.com> (raw)
In-Reply-To: <AANLkTimLnBoEeBmFJHEWc1vQZuvPPsp920JWyQK8CABn@mail.gmail.com>
References: <AANLkTimLnBoEeBmFJHEWc1vQZuvPPsp920JWyQK8CABn@mail.gmail.com>
On Thu, Jan 27, 2011 at 11:20 PM, Amitabh Kant <amitabhkant@gmail.com>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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: amitabhkant@gmail.com
Subject: Re: Automating PostgreSql table partition using triggers
In-Reply-To: <AANLkTimuyMX0WDdYg67CLzpSe0DV00jVkM90g5P0ABCG@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox