From: | "Guy Rouillier" <guyr(at)masergy(dot)com> |
---|---|
To: | "PostgreSQL Interfaces" <pgsql-interfaces(at)postgresql(dot)org> |
Subject: | PL/pgsql dynamic statements and null values |
Date: | 2005-03-07 19:28:44 |
Message-ID: | CC1CF380F4D70844B01D45982E671B2348E6AD@mtxexch01.add0.masergy.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2005-03-07 21:21:49 | Re: [INTERFACES] bcc32.mak for libpq broken? (distro 8.0.0) |
Previous Message | Moncef Mezghani | 2005-03-07 10:11:45 | undefined reference to `SSL_new' when linking with lib-pq.a version 7.4.7-2 |