Thread: problem with trigger function

problem with trigger function

From

Susan Cassidy

Date:

07 March 2014, 03:08:52

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:
ERROR: missing FROM-clause entry for table "new"
LINE 1: insert into metric_double_values_201203 values (NEW.*)
^
QUERY: insert into metric_double_values_201203 values (NEW.*)
CONTEXT: PL/pgSQL function metric_double_insert_func() line 7 at EXECUTE statement

I don't know what from clause it is talking about

This is a trigger for inserting rows into the proper partition table based on date.

Any help appreciated.

Thanks,
Susan

Re: problem with trigger function

From

Adrian Klaver

Date:

07 March 2014, 03:21:40

On 03/06/2014 04:08 PM, Susan Cassidy wrote:
> 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;

The basic problem is here:

insert_sql:='insert into metric_double_values_' ||
to_char(NEW.datetimeval,'YYYYMM') || ' values (NEW.metricID,
NEW.sourceID, NEW.timestamp, NEW.value, NEW.datetimeval)';

in particular:

  ' values (NEW.metricID,...'

You are quoting the NEW values which Postgres then interprets as values
coming from the table new as new.metric_id, etc.

You need to use the parameter placeholders,$1, $2, etc. See here for
some examples:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

40.5.4. Executing Dynamic Commands

>
> 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:
> ERROR:  missing FROM-clause entry for table "new"
> LINE 1: insert into metric_double_values_201203 values (NEW.*)
>                                                          ^
> QUERY:  insert into metric_double_values_201203 values (NEW.*)
> CONTEXT:  PL/pgSQL function metric_double_insert_func() line 7 at
> EXECUTE statement
>
> I don't know what from clause it is talking about
>
> This is a trigger for inserting rows into the proper partition table
> based on date.
>
> Any help appreciated.
>
> Thanks,
> Susan


--
Adrian Klaver
adrian.klaver@aklaver.com