Re: EXECUTE INSERT BUGS?

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

On 11/7/06, Matthew Peter <survivedsushi(at)yahoo(dot)com> 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
>
>
>
>
>
> ____________________________________________________________________________________
> Cheap talk?
> Check out Yahoo! Messenger's low PC-to-Phone call rates.
> http://voice.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>