Re: another trigger problem

Bingo, this COPY file did not have the datetimeval, so I added a few lines
of code to convert it from the ctime-type entry that exists in the record.
You would think that postgres could have output a more helpful error
message, though.

Thanks a lot for the assist.

Susan

On Fri, Mar 7, 2014 at 3:18 PM, Rob Sargent <robjsargent(at)gmail(dot)com> wrote:

> 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?
>
>
>
> --
> 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
>