You should be able to use a CASE WHEN statement inside this expression and test for a null itemIdValue. If it is null the you can return ''NULL'' else, return itemIdValue. HTH On Friday 18 February 2005 09:07 am, David Gagnon saith: > Hi all, > > I did a stored procedure and ran into this small problem. > > Here itemIdValue may be null and I would have expected quote_literal to > returns null as a string or the value quoted. For now I think it returns a > real null. This causes my function to crash. > > insertStatement:= ''INSERT INTO IL ( ILNUM, ILTYPE, ILICNUM, ILRRNUM, > ILQTE, ILPRIX, ts ) VALUES ( '' || idValue ||'', ''|| typeValue ||'',''; > insertStatement:= insertStatement || > quote_literal(itemIdValue) || '',''; > insertStatement:= insertStatement || > > > I haven't found an existing function to handle this case and I hate > reinventing the Wheel... Is there a function that already handles that? > > > For now I worked around this by using the COALESCE function. > > COALESCE(quote_literal(itemIdValue),'' null'') || '',''; > insertStatement:= insertStatement || > > Thanks for your help > > > /David > > > > __________________________________ > Do you Yahoo!? > Yahoo! Mail - Find what you need with new enhanced search. > http://info.mail.yahoo.com/mail_250 > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
