Thread: quote_literal with NULL
Hi hackers, I note that if you pass NULL to quote_literal(), you get NULL. This isn't surprising, but I was thinking that the stated purpose of quote_literal is preparing the argument for entry into a dynamic SQL statement. In this context, it fails for NULL input. Wouldn't it be more useful if quote_literal(NULL) yielded the text value 'NULL'? With the current behaviour, if you want quote_literal to be "null safe" you have to replace any such calls with coalesce(quote_literal(foo), 'NULL')). Since the use case for quote_literal is concatenating the result with some other text, a NULL return seems guaranteed to be unhelpful. Meanwhile, the string 'NULL' is the only way of representing a NULL in SQL, so it makes sense (to me) that this is what quote_literal should output. Comments? Cheers, BJ
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. 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. 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' -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
On 10/10/07, Simon Riggs <simon@2ndquadrant.com> 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
>>> On Wed, Oct 10, 2007 at 4:57 AM, in message <37ed240d0710100257r149a8d2cmb671b69a1673eb54@mail.gmail.com>, "Brendan Jurd" <direvus@gmail.com> wrote: > On 10/10/07, Simon Riggs <simon@2ndquadrant.com> 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. In particular, it seems like exactly what you would want for values you're going to use in an INSERT or the SET clause of an UPDATE. -Kevin
On Wed, 2007-10-10 at 10:12 -0500, Kevin Grittner wrote: > >>> On Wed, Oct 10, 2007 at 4:57 AM, in message > <37ed240d0710100257r149a8d2cmb671b69a1673eb54@mail.gmail.com>, "Brendan Jurd" > <direvus@gmail.com> wrote: > > On 10/10/07, Simon Riggs <simon@2ndquadrant.com> 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. > > In particular, it seems like exactly what you would want for values > you're going to use in an INSERT or the SET clause of an UPDATE. Perhaps have quote_nullable() then as well? You then use quote_nullable() in INSERT and UPDATE SET clauses and quote_literal() in SELECT WHERE clauses. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > Perhaps have quote_nullable() then as well? > > You then use quote_nullable() in INSERT and UPDATE SET clauses and > quote_literal() in SELECT WHERE clauses. I still don't see the use case. Wouldn't your app still need to check for nullability anyway, to avoid " = NULL"? (Aside: seems to me that SET foo = NULL; really should be SET foo TO NULL; to be consistent with WHERE foo IS NULL;) - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200710101221 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFHDPwyvJuQZxSWSsgRAwGaAJ92ICR+MyclkmWvJRkC4vazIw+b0ACghpZt WXbCxe0abFlp8jwr0ol/fac= =oWqD -----END PGP SIGNATURE-----
"Greg Sabino Mullane" <greg@turnstep.com> writes:
>> Perhaps have quote_nullable() then as well?
>>
>> You then use quote_nullable() in INSERT and UPDATE SET clauses and
>> quote_literal() in SELECT WHERE clauses.
> I still don't see the use case. Wouldn't your app still need to check
> for nullability anyway, to avoid " = NULL"?
Well, it's clearly useful in INSERT and UPDATE. For WHERE cases, you
might or might not be able to use it, but I note that quote_nullable()
would work much more like what happens if you use a parameter symbol
and then bind NULL as the actual parameter value ...
In hindsight we should probably have done quote_literal the way the OP
suggests, but I concur that it's too late to change it. An additional
function seems a reasonable compromise.
regards, tom lane
On Oct 10, 2007, at 11:24 , Greg Sabino Mullane wrote: > (Aside: seems to me that > SET foo = NULL; really should be SET foo TO NULL; to be consistent > with WHERE foo IS NULL;) The = character has different meanings in these two cases. UPDATE foos SET foo = NULL -- assignment WHERE bar IS NULL -- comparison AND foo = 'ignore me' -- comparison Or is that what the smiley was about? :) Michael Glaesemann grzm seespotcode net
On 10/11/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Well, it's clearly useful in INSERT and UPDATE. For WHERE cases, you > might or might not be able to use it, but I note that quote_nullable() > would work much more like what happens if you use a parameter symbol > and then bind NULL as the actual parameter value ... > > In hindsight we should probably have done quote_literal the way the OP > suggests, but I concur that it's too late to change it. An additional > function seems a reasonable compromise. quote_nullable() works for me. I'll write up a patch. Cheers, BJ