another trigger problem

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@gmail.com> wrote:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general