On Fri, Jan 28, 2011 at 2:40 AM, Viktor Bojović
<viktor.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=new. testing_name;
Field1=new.testing_id;
sql:='insert into tableName'||id||' (testing_id,testing_name) values ('||Field1||','||quote_literal(Field2)||')';
exec(sql);
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
Thanks Victor. This should work out for me.
Amitabh