Thread: Create Trigger Function For Table Partition.

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

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.