Re: Automating PostgreSql table partition using triggers

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=new. testing_name;
Field1=new.testing_id;
sql:='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 <amitabhkant(at)gmail(dot)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
>
>

--
---------------------------------------
Viktor Bojović
---------------------------------------
Wherever I go, Murphy goes with me