Re: 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(at)postgresql(dot)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.