Search Postgresql Archives

Re: EXECUTE INSERT BUGS?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux