We have a stored procedure that takes a bunch of parameters (integer,
varchar and timestamp) and then builds up an INSERT statement with these
values using || concatenation (determining the target table depends on
the value of the timestamp.) I'm getting "Cannot execute a null
statement" when running this stored proc. I finally figured out using
RAISE NOTICE that one of the integer values was null, which was causing
the entire concatenation to apparently be null.
Several questions:
(1) My first attempt to fix this was to try this:
myIntParam := COALESCE(myIntParam, 0);
Where myIntParam is one of the incoming integer values in the function
argument list. This failed because "myIntParam is declared CONSTANT."
Well, it isn't, so I'm guessing this is the default value? Is there any
way to declare that function arguments are *not* constant? I tried
volatile and that didn't work. I don't want to have to define local
variables for every function argument.
(2) To get this working, I declared a local variable localMyIntParam,
COALESCEd it and was able to get the insert statement to work. I then
realized this column in the database is defined as nullable, so if the
incoming value is null, I really want to concatenate NULL into the
insert string. Unfortunately, I don't think COALESCE will work because
myIntParam is declared integers, so I can't assign the string "NULL" to
it. To be safe, I'll probably have to validate all incoming arguments.
Does pgsql provide any shorthand notation to check a value for null and
insert NULL in its place, so I don't have to do something like this for
every argument:
if (myIntParam is null) then sqlstmt := sqlstmt || ', NULL';else sqlstmt := sqlstmt || ', ' || myIntParam;end
if
Thanks.
--
Guy Rouillier