Create Trigger Function For Table Partition.

I had solved my second problem using the following technique :

EXECUTE 'INSERT INTO ' || measurement_table_name || '(fk_unit_id, v) VALUES
(' || NEW.fk_unit_id || ',' || NEW.v || ')';

yccheok wrote:

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
View this message in context: http://old.nabble.com/Create-Trigger-Function-For-Table-Partition.-tp27319259p27319924.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.