Re: quote_literal with NULL

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