PL/pgsql dynamic statements and null values

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

Browse pgsql-interfaces by date

  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