Thread: another trigger problem
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
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();
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();
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.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();
Anyone have any ideas?
Thanks,
Susan
Thanks,
Susan
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 > > -- Adrian Klaver adrian.klaver@aklaver.com
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?
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
Thanks a lot for the assist.
Susan
On Fri, Mar 7, 2014 at 3:18 PM, Rob Sargent <robjsargent@gmail.com> wrote:
On 03/07/2014 04:06 PM, Adrian Klaver wrote:On 03/07/2014 02:48 PM, Susan Cassidy wrote:Is the datetimeval always non-null?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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Sat, Mar 8, 2014 at 12:36 AM, Susan Cassidy <susan.cassidy@decisionsciencescorp.com> wrote: > You would think that postgres could have output a more helpful error > message, though. I believe that is the correct message: you were concatenating a null string to something, and so nullifying the string you were using for execute. In other words, it is not a wrong error message, or it could not have been better since the instruction before the execute was doing what you asked for. I believe in such cases it is better to check against the query string and execute it only if valid. Luca