another trigger problem

From: Susan Cassidy <susan(dot)cassidy(at)decisionsciencescorp(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: another trigger problem
Date: 2014-03-07 22:48:59
Message-ID: CAE3Q8om_fDrv65fKzSOM=grNuJvhs2vRt9eDYoBm8J-f21J_Lg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Thanks,
Susan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2014-03-07 23:06:04 Re: another trigger problem
Previous Message Adrian Klaver 2014-03-07 21:07:36 Re: Mysterious DB reset