Re: EXECUTE INSERT BUGS?

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