Thread: EXECUTE INSERT BUGS?

create table test (col text);
create or replace function tester() RETURNS void AS $$
DECLARE
    cmd text;
    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;

END;
$$   LANGUAGE plpgsql;


test=# \i /tmp/test
CREATE TABLE
CREATE FUNCTION
test=# select * from tester();
ERROR:  cannot EXECUTE a null querystring
CONTEXT:  PL/pgSQL function "tester" line 12 at execute statement


Also, if v_value is set to boolean then quote_literal(v_value) throws error




____________________________________________________________________________________
Cheap talk?
Check out Yahoo! Messenger's low PC-to-Phone call rates.
http://voice.yahoo.com

On Mon, 2006-11-06 at 16:40 -0800, Matthew Peter wrote:
> create table test (col text);
> create or replace function tester() RETURNS void AS $$
> DECLARE
>     cmd text;
>     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;
>
> END;
> $$   LANGUAGE plpgsql;
>
>
> test=# \i /tmp/test
> CREATE TABLE
> CREATE FUNCTION
> test=# select * from tester();
> ERROR:  cannot EXECUTE a null querystring
> CONTEXT:  PL/pgSQL function "tester" line 12 at execute statement
>
>
> Also, if v_value is set to boolean then quote_literal(v_value) throws error
>

Concatenation with NULL yields NULL, which is the correct behavior.
Also, passing NULL to most functions results in NULL. That means your
whole query is NULL when you execute it. Instead, use COALESCE() to make
v_value non-NULL if you need to.

Regards,
    Jeff Davis

Hi Mathew,

whats happening here in your case is that when you use the concatenation operator || and an element in your command is NULL the whole concat chain ends up being NULL so the execute command runs as

EXECUTE NULL

what you can do is to write your command as such:

cmd := 'INSERT INTO test (
               col
       ) values ( '
               ||coalesce( quote_literal(v_value),'NULL') ||  ');';

Now your command will look like

EXECUTE 'insert into test (col) values( NULL);

making NULL a string instead of a value.

Regards
Talha Khan

Jeff Davis <pgsql@j-davis.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?

            regards, tom lane

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