On 10/10/07, Simon Riggs <[EMAIL PROTECTED]> wrote: > On Wed, 2007-10-10 at 14:57 +1000, Brendan Jurd wrote: > > > Wouldn't it be more useful if quote_literal(NULL) yielded the text value > > 'NULL'? > > I don't think you can change that now. There could be code out there > that relies on that behaviour. >
Bummer. But I take your point. If there's a good chance someone is going to have their application murdered by a change here, best to leave it alone. I've already gotten around this in my own apps by adding a UDF alternative to quote_literal that plays nicely with NULLs, but thought I'd mention it here in case others were of the same mind. > It isn't very helpful to return the word NULL in many cases, since the > WHERE clause "col = NULL" does not do the same thing as "col is NULL". > So you need to know about NULL values and how to handle them in many > cases. > Well if you're expecting a possibly-NULL value in your dynamic query you're going to be using something like 'WHERE foo IS NOT DISTINCT FROM ' || quote_literal(bar) anyway. Either way possibly-NULL values need to be anticipated and treated specially. With the string 'NULL' you need DISTINCT FROM. With an actual NULL you need COALESCE. It just seemed to me that the string 'NULL' result was more in line with what quote_literal was supposed to do; and leads to less cluttered code. > It might be useful to define a new text concatenation operator ||| that > treats NULL values as zero-length strings, so that > 'help ' ||| NULL ||| 'me' returns 'help me' > That could be cool. Not immediately practical for the dynamic query scenario though: If I do 'WHERE foo IS NOT DISTINCT FROM ' ||| quote_literal(bar) it'll still give me an invalid query string if bar is NULL. Cheers, BJ ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
