I'm having a problem with a trigger function. I've been googling for over an hour, with no luck with my specific problem.
I get this error:
ERROR: missing FROM-clause entry for table "new"
LINE 1: insert into metric_double_values_201203 values (NEW.metricID...
^
QUERY: insert into metric_double_values_201203 values (NEW.metricID, NEW.sourceID, NEW.timestamp, NEW.value, NEW.datetimeval)
CONTEXT: PL/pgSQL function metric_double_insert_func() line 8 at EXECUTE statement
From this trigger function:
CREATE OR REPLACE FUNCTION metric_double_insert_func()
RETURNS TRIGGER AS $$
DECLARE insert_sql text;
BEGIN
insert_sql:='insert into metric_double_values_' || to_char(NEW.datetimeval,'YYYYMM') || ' values (NEW.metricID, NEW.sourceID, NEW.timestamp, NEW.value, NEW.datetimeval)';
EXECUTE insert_sql using NEW;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
DROP TRIGGER insert_metric_double_insert_trigger on metric_double_values;
CREATE TRIGGER insert_metric_double_insert_trigger
BEFORE INSERT ON metric_double_values
FOR EACH ROW EXECUTE PROCEDURE metric_double_insert_func();
This was an attempt at eliminating the error I got when trying to insert with values (NEW.*) using NEW:
CONTEXT: PL/pgSQL function metric_double_insert_func() line 7 at EXECUTE statement
