Re: EXECUTE INSERT BUGS?

On Tue, 2006-11-07 at 00:01 -0500, Tom Lane wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> 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