Create Trigger Function For Table Partition.

[Yan Cheng Cheok <yccheok@xxxxxxxxx>]

By referring to http://www.postgresql.org/docs/current/static/ddl-partitioning.html

(1) I create trigger function as follow :

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS 
$BODY$DECLARE
    measurement_table_index bigint;
    measurement_table_name text;
BEGIN
    measurement_table_index = NEW.measurement_id % 20;
    measurement_table_name = 'measurement_' || measurement_table_index;
    EXECUTE 'INSERT INTO ' || measurement_table_name || ' VALUES (NEW.*);';
    RETURN NULL;
END;$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON measurement
    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();


I can see my trigger function named "measurement_insert_trigger" under pgadmin. However, I cannot see "insert_measurement_trigger"

http://sites.google.com/site/yanchengcheok/Home/cannot-see-trigger-point.PNG?attredirects=0

How can I see "insert_measurement_trigger" under pgadmin?

(2) When I execute SELECT * FROM create_lot();

CREATE OR REPLACE FUNCTION create_lot()
  RETURNS void AS
$BODY$DECLARE
    _lot_id bigint;
    _unit_id bigint;    
    count int;
    count2 int;   
BEGIN 
    INSERT INTO lot (v) VALUES ('Lot0') RETURNING lot_id INTO _lot_id;
    count = 1;    
    LOOP 
        INSERT INTO unit (fk_lot_id, v) VALUES (_lot_id, 'Unit') RETURNING unit_id INTO _unit_id;
       
        count2 = 1;
        LOOP
            INSERT INTO measurement (fk_unit_id, v) VALUES (_unit_id, 'Measurement');        
            count2 = count2 + 1;
            EXIT WHEN count2 > 3;
        END LOOP;
        
        count = count + 1;
        EXIT WHEN count > 3;
    END LOOP;  
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION create_lot() OWNER TO postgres;

I get the following error :

ERROR:  NEW used in query that is not in a rule
LINE 1: INSERT INTO measurement_9 VALUES (NEW.*);
                                          ^
QUERY:  INSERT INTO measurement_9 VALUES (NEW.*);
CONTEXT:  PL/pgSQL function "measurement_insert_trigger" line 7 at EXECUTE statement
SQL statement "INSERT INTO measurement (fk_unit_id, v) VALUES ( $1 , 'Measurement')"
PL/pgSQL function "create_lot" line 14 at SQL statement

It seems that NEW is not being recognized within EXECUTE statement. How can I avoid this problem? I cannot have "static SQL", as my table name needed to be dynamic generated.

Thanks and Regards
Yan Cheng CHEOK


      


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general