Re: another trigger problem

On 03/07/2014 04:06 PM, Adrian Klaver wrote:
> On 03/07/2014 02:48 PM, Susan Cassidy wrote:
>> I have another problem with a slightly different trigger.  It's very
>> weird, because it is exactly the same as the first trigger, that now
>> works, except for the table name.
>>
>> The error is:
>>
>> ERROR:  query string argument of EXECUTE is null
>> CONTEXT:  PL/pgSQL function metric_int_insert_func() line 5 at EXECUTE
>> statement
>>
>>
>> The trigger is:
>>
>> CREATE OR REPLACE FUNCTION metric_int_insert_func()
>> RETURNS TRIGGER AS $$
>>    DECLARE insert_sql text;
>> BEGIN
>>      insert_sql:='insert into metric_int_values_' ||
>> to_char(NEW.datetimeval,'YYYYMM') || ' values ($1.*)';
>>      EXECUTE insert_sql using NEW;
>>      RETURN NULL;
>> END;
>> $$
>> LANGUAGE plpgsql;
>>
>> DROP TRIGGER IF EXISTS insert_metric_int_insert_trigger on
>> metric_int_values;
>> CREATE TRIGGER insert_metric_int_insert_trigger
>>          BEFORE INSERT ON metric_int_values
>>          FOR EACH ROW EXECUTE PROCEDURE metric_int_insert_func();
>>
>>
>> which is exactly the same as this one that works:
>> 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 ($1.*)';
>>      EXECUTE insert_sql using NEW;
>>      RETURN NULL;
>> END;
>> $$
>> LANGUAGE plpgsql;
>>
>> DROP TRIGGER IF EXISTS 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();
>>
>>
>> I can't seem to figure it out.  I've retyped some of the lines, in case
>> there is a weird character somewhere, but they got there with a vi yank
>> and put, so that's not likely.
>>
>> Anyone have any ideas?
>
> Try dropping the function and then creating it, instead of just the
> create and replace. I have seen issues in the past with a stale copy
> of a function causing a problem.
>
>>
>> Thanks,
>> Susan
>>
>>
Is the datetimeval always non-null?