PL/pgsql dynamic statements and null values
От | Guy Rouillier |
---|---|
Тема | PL/pgsql dynamic statements and null values |
Дата | |
Msg-id | CC1CF380F4D70844B01D45982E671B2348E6AD@mtxexch01.add0.masergy.com обсуждение исходный текст |
Список | pgsql-interfaces |
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
В списке pgsql-interfaces по дате отправления: