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@yahoo.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
