On Tue, 2006-11-07 at 00:01 -0500, Tom Lane wrote:
> Jeff Davis <pgsql@xxxxxxxxxxx> writes:
> > On Mon, 2006-11-06 at 16:40 -0800, Matthew Peter wrote:
> >> v_value text := null;
> >> -- ^^^ right here, NULL makes the querystring fail by setting cmd =
> >> null
> >> BEGIN
> >> cmd := 'INSERT INTO test (
> >> col
> >> ) values ( '
> >> || quote_literal(v_value) || ');';
> >> EXECUTE cmd;
>
> > Concatenation with NULL yields NULL, which is the correct behavior.
>
> Hm. I wonder whether we should redefine quote_literal as a non-strict
> function that delivers "NULL" (*without* any quotes) when fed a null
> input. While that would do the Right Thing in this particular example,
> I'm worried that it might do the wrong thing in other contexts...
> Comments?
>
One potential problem is if someone is passing a statement to EXECUTE
like:
SELECT 'foo'
'bar';
Then they could potentially end up with a statement like:
SELECT NULL
NULL;
If the values of two variables were NULL instead of 'foo' and 'bar'.
If the author of the function uses COALESCE() before quote_literal(),
he'd be fine, but if he used it afterward, his function would stop
working. There are similar situations in other places where the SQL
standard treats NULL differently from a string literal. For instance:
SELECT INTERVAL '0 minutes';
Again, if they COALESCE() to (for example) '0 minutes' after the
quote_literal, it will fail. If they COALESCE() before, it will of
course work fine.
Also:
IF foo = bar -- fails
IF quote_literal(foo) = quote_literal(bar) -- succeeds
Also, it would change the bahavior when calling quote_literal() on the
return from a previous quote_literal().
We could avoid potential confusion (if there is any) by making a new
function with a name that better communicates what it does. Is there a
name that means "converts a value into a string that would evaluate to
that value"?
I'm not arguing against changing it to non-strict, it probably avoids
more confusion than it would cause.
Regards,
Jeff Davis
